Display Data without using Pivot.

Question

Hi

I have below data. Is there any way it can be done without using Pivot. Catg can be more than 40.

Item   Amount   Catg

A001   100   Accounts
A002   200   Accounts
A002   400   Accounts
A003   600   Hr
A004   800   Sales
I want to display like it below

Item   Amount   Accounts   HR   Sales

A001   100   100
A002   200   600
A003   600      600
A004   800
800

Thanks

Jignesh Kumar · Answer

Hello Ramco, You can use Case When with Stuff(), Please change your table name and column name as per your requirement DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX); SELECT @cols = STUFF((SELECT ', MAX(CASE WHEN Catg = ''' + Catg + ''' THEN Amount END) AS [' + Catg + ']' FROM (SELECT DISTINCT Catg FROM your_table) AS CategoryList FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, ''); SET @sql = ' SELECT Item, Amount, ' + @cols + ' FROM your_table GROUP BY Item, Amount ORDER BY Item;'; -- Step 3: Execute dynamic SQL EXEC sp_executesql @sql;

Sophia Carter · Answer

Absolutely! To achieve the desired display without using Pivot, you can employ SQL's conditional aggregation. This method utilizes CASE statements to categorize data into separate columns based on conditions. Here's how you can approach this: SELECT Item, Amount, MAX(CASE WHEN Catg = 'Accounts' THEN Amount END) AS Accounts, MAX(CASE WHEN Catg = 'Hr' THEN Amount END) AS HR, MAX(CASE WHEN Catg = 'Sales' THEN Amount END) AS Sales FROM your_table GROUP BY Item, Amount; In this SQL query: - We use CASE statements within the aggregation functions to sort the data into separate columns based on the 'Catg' categories. - The MAX function consolidates the Amount values according to the respective categories. - Finally, we GROUP BY Item and Amount to display the data as you specified. By executing this SQL query with your dataset, you should achieve the desired outcome displayed in a tabular format without relying on the Pivot function.

Muhammad Imran Ansari · Answer

Hello Ramco, If Catg contains more than 40 dynamic values, you’ll need Dynamic SQL: DECLARE @ColumnNames NVARCHAR(MAX); DECLARE @SQLQuery NVARCHAR(MAX); -- Generate dynamic column list SELECT @ColumnNames = STRING_AGG('SUM(CASE WHEN Catg = ''' + Catg + ''' THEN Amount ELSE 0 END) AS [' + Catg + ']', ', ') FROM (SELECT DISTINCT Catg FROM YourTable) AS cols; -- Construct Dynamic SQL Query SET @SQLQuery = ' SELECT Item, SUM(Amount) AS Total_Amount, ' + @ColumnNames + ' FROM YourTable GROUP BY Item;'; -- Execute the query EXEC sp_executesql @SQLQuery; Good Luck!

Ramco Ramco · Answer

Hi Sophia If there are more than 40 Catg then i have to write 40 Case Statements Thanks