How to pivot columns data to rows

Question

Hi team, i have a query where i need to swap columns data to rows. below is the example of table data and required format.

Table data:

Product size manualSales ScanningSales TotalSales
AC 0 0 0
AF 0 0 0
AP 0 0 0
BS 0 0 0
CP 0 219 219
DD 0 2 2

Output

Product_Size   AC   AF   AP   BS   CP   DD   TotalSales
ScanningSales   0   0   0   0   219   0   219
ManualSales       0   0   0   0   0   2 2

Total 0 0 0 0 0 2 2

Please help me opn this...

Note: In table product size column data doesnot fix as above ,sizes comes static in future.

Uday Dodiya · Answer

Create Table Create Table Product_Pivot ( ID int Identity, Product_Size varchar(4), Manual_Sales int, Scanning_Sales int, Total_Sales int) Insert Data Insert Into Product_Pivot Values('AC',0,0,0), ('AF',0,0,0),('AP',0,0,0),('BS',0,0,0),('CP',0,219,219),('DD',0,2,2) Query - This is dynamic query if there is more than above row the below query also give output DECLARE @columns NVARCHAR(MAX) DECLARE @query NVARCHAR(MAX) -- Get a list of column names SELECT @columns = COALESCE(@columns + ', ', '') + QUOTENAME(Product_Size) FROM Product_Pivot GROUP BY Product_Size ORDER BY Product_Size -- Build the query using the column names SET @query = ' SELECT ColumnName,' + @columns + ' FROM ( SELECT Product_Size, ''Scanning_Sales'' as ColumnName, Scanning_Sales as Sales FROM Product_Pivot UNION SELECT Product_Size, ''Manual_Sales'' as ColumnName, Manual_Sales as Sales FROM Product_Pivot UNION SELECT Product_Size, ''Total_Sales'' as ColumnName, Total_Sales as Sales FROM Product_Pivot ) x PIVOT ( SUM(Sales) FOR Product_Size IN (' + @columns + ') ) p' --Execute the query exec(@query) Output ColumnName AC AF AP BS CP DD Manual_Sales 0 0 0 0 0 0 Scanning_Sales 0 0 0 0 219 2 Total_Sales 0 0 0 0 219 2

Uday Dodiya · Answer

Another Solution , Here he don't want to add total_sales data, we get that data using sql and here we also get all columns total at the end Create Table Create Table Product_Pivot ( ID int Identity, Product_Size varchar(4), Manual_Sales int, Scanning_Sales int) Insert Data Insert Into Product_Pivot Values('AC',1,5), ('AF',2,7),('AP',0,5),('BS',0,9),('CP',1,219),('DD',1,2) Query DECLARE @columns_sum NVARCHAR(MAX) DECLARE @columns NVARCHAR(MAX) DECLARE @query NVARCHAR(MAX) -- Get a list of column names SELECT @columns = COALESCE(@columns + ', ', '') + QUOTENAME(Product_Size) FROM Product_Pivot GROUP BY Product_Size ORDER BY Product_Size SELECT @columns_sum = COALESCE(@columns_sum + '+ ', '') + QUOTENAME(Product_Size) FROM Product_Pivot GROUP BY Product_Size ORDER BY Product_Size -- Build the query using the column names SET @query = ' SELECT Product_Size_Heading,' + @columns + ',(' + @columns_sum + ') As Total FROM ( SELECT Product_Size, ''Scanning_Sales'' as Product_Size_Heading, Scanning_Sales as Sales FROM Product_Pivot UNION SELECT Product_Size, ''Manual_Sales'' as Product_Size_Heading, Manual_Sales as Sales FROM Product_Pivot UNION SELECT Product_Size, ''Total_Sales'' as Product_Size_Heading, (Scanning_Sales+Manual_Sales) as Sales FROM Product_Pivot ) x PIVOT ( SUM(Sales) FOR Product_Size IN (' + @columns + ') ) p' --Execute the query exec(@query) Output Product_Size_Heading AC AF AP BS CP DD Total Manual_Sales 1 2 0 0 1 1 5 Scanning_Sales 5 7 5 9 219 2 247 Total_Sales 6 9 5 9 220 3 252

Jignesh Kumar · Answer

Hello, Please use this link which will help you out, https://www.c-sharpcorner.com/UploadFile/f0b2ed/pivot-and-unpovit-in-sql-server/ https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/