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

Answer 1

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;

Answer 2

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.

Answer 3

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!

Answer 4

Hi Sophia

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

Thanks