Display Data without using Pivot.

Ramco Ramco

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

4 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jignesh Kumar1y ago

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;

Muhammad Imran Ansari1y ago

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 Ramco1y ago
Hi Sophia

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

Thanks
0
Sophia Carter1y ago
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.
+1