How to pivot columns data to rows

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

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

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/