Display rows as columns

Ramco Ramco

4y834

I have below data ,

Code	Name	Quantity	Dscription
32	Rohan	1	Item 1
32	Rohan	2	Item 2
36	Sunny	1	Item 2

I want to display like below

Name	Item 1	Item 2
Rohan	1	2
Sunny		1
Grand Total	1	3

Thanks

3 Replies

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

Muhammad Imran Ansari4y ago
@Ramco,
For dynamic, you should go with dynamic query. I have modified the query in which description columns could be any.
DECLARE @DescriptionCols VARCHAR(200)
DECLARE @ForGrandTotal VARCHAR(200)
SELECT @DescriptionCols = COALESCE(@DescriptionCols + ',[','[') + [Dscription] + ']'
FROM   (SELECT DISTINCT [Dscription]
        FROM   [dbo].[YourTable]) t

SELECT @ForGrandTotal = COALESCE(@ForGrandTotal + ',SUM([','SUM([') + [Dscription] + '])'
FROM   (SELECT DISTINCT [Dscription]
        FROM   [dbo].[YourTable]) t

DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery =
'SELECT * INTO #FinalResult FROM
(
    SELECT [Name], Quantity, Dscription
    FROM
        [dbo].[YourTable]
) t
PIVOT(
    MAX([Quantity])
    FOR Dscription IN (' + @DescriptionCols + ')
) AS pivot_table

SELECT *
FROM #FinalResult
UNION ALL
SELECT ''Grand Total'', ' + @ForGrandTotal + '
FROM #FinalResult
'
EXEC(@SQLQuery)
Ramco Ramco4y ago
Hi Muhammad

Item1 , Item2 should be dynamic. IT can be Item3 , Item4

Thanks
Muhammad Imran Ansari4y ago
You can achive this through PIVOT operator. Use first query to convert your table and then with second query calculate grand total.
Query-1:
SELECT * INTO #FinalResult FROM
(
    SELECT [Name], Quantity, Dscription
    FROM
        [dbo].[YourTable]
) t
PIVOT(
    MAX([Quantity])
    FOR Dscription IN (
        [Item 1],
        [Item 2])
) AS pivot_table;
Query-2:
SELECT *
FROM #FinalResult
UNION ALL
SELECT 'Grand Total', SUM([Item 1]), SUM([Item 2])
FROM #FinalResult
Final Result: