Display rows as columns

Question

Hi 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

Muhammad Imran Ansari · Answer

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:

Muhammad Imran Ansari · Answer

@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 Ramco · Answer

Hi Muhammad Item1 , Item2 should be dynamic. IT can be Item3 , Item4 Thanks

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

Display rows as columns

Insert Link

Embed YouTube Video

Table Options

Insert Image

Answers (3)