Dynamically Pivot a SQL data table with summary

Question

I have the following data table which is generated from a process of reading a barcode and packing some garments accordingly, what I'm trying to do is to pivot this table to have the summary of this packing process.

My source table:

And the result which I'm trying to reach is like:

I tried to do it with the following code but I'm having problems with the result which is returning only one size and all the colors.

IF OBJECT_ID('tempdb..##TBL_TEMP') IS NOT NULL
    DROP TABLE ##TBL_TEMP

-- This parameter will hold the dynamically created SQL script
DECLARE @SQLQuery AS NVARCHAR(MAX)

--This parameter will hold the Pivoted Column values
DECLARE @PivotColumns AS NVARCHAR(MAX)

SELECT @PivotColumns = (SELECT DISTINCT [Size])
FROM [dbo].[Tabel]

--Create the dynamic query with all the values for
--pivot column at runtime
--LIST ALL FILEDS EXCEPT PIVOT COLUMN

SET @SQLQuery =
   N'SELECT [ColorName]' +   @PivotColumns + '
   INTO ##TBL_TEMP
   FROM [dbo].[Tabel]

   PIVOT( MAX([Qty])
      FOR [Size] IN (' + @PivotColumns + ')) AS Q'

--Execute dynamic query
EXEC sp_executesql @SQLQuery

/* VIEW PIVOTED TABLE RESULTS */
Select * from ##TBL_TEMP

Could you please kindly help me to have the result using C#

Answer 1

I think you are missing small things in your query. Try these small changes in your pivot columns and the Select statement of your dynamic query.

Just add the BoxNo and StyleCode in your dynamic SQL select query and PIVOT the data for SUM[Qty]

 SET   @SQLQuery =
 N'SELECT [BoxNo],[StyleCode],[ColorName],' +   @PivotColumns + '
 INTO ##TBL_TEMP
 FROM [dbo].[Tabel]

 PIVOT(SUM([Qty]) 
   FOR [Size] IN (' + @PivotColumns + ')) AS Q'

 --Execute dynamic query
 EXEC sp_executesql @SQLQuery

 --VIEW PIVOTED TABLE RESULTS--
 SELECT * FROM  ##TBL_TEMP

Please see the screenshot of my sample data

Try this piece of code now :

Select BoxNo,StyleCode,ColorName,Size,Qty 
INTO #tbl_1 
FROM [dbo].[1202-904200]

DECLARE   @SQLQuery AS NVARCHAR(MAX)
DECLARE  @PivotColumns AS NVARCHAR(MAX)
SELECT  @PivotColumns= ISNULL(@PivotColumns + ',','') + QUOTENAME([Size]) FROM 
(SELECT DISTINCT [Size] FROM #tbl_1) as Size 

--Create the dynamic query with all the values for pivot column at runtime.
SET @SQLQuery =  N'SELECT [BoxNo],[StyleCode],[ColorName],' + @PivotColumns + '
                 INTO ##TBL_TEMP
                 FROM #tbl_1 -- Replace your source here to #tbl_1
                 PIVOT(SUM([Qty]) 
                 FOR [Size] IN (' + @PivotColumns + ')) AS Q'

--Execute dynamic query
EXEC sp_executesql @SQLQuery

/* VIEW PIVOTED TABLE RESULTS */
SELECT * FROM  ##TBL_TEMP