Dynamic SQL Server Pivot Table
10,562
Remove the ORDER_ID
from the selection, and select the column name
:
SET @query = 'SELECT ' + @cols + ' from
(
SELECT ORDER_ID, DSC_NAME -- <--- you didn't select the name here
FROM test
) x
pivot
(
MIN(ORDER_ID)
for DSC_NAME in (' + @cols + ')
) p '
And use MAX(DSC_Name)
instead of MIN(ORDER_ID)
. Like this:
SET @query = 'SELECT '+ @cols + ' from
(
SELECT DSC_NAME, Name
FROM test
) x
pivot
(
MAX(DSC_Name)
for NAME in (' + @cols + ')
) p ';
SQL Fiddle Demo
This will give you:
| DWG_DOC | EQIP_1 | EQIP_2 | PART | PRCS |
---------------------------------------------------------
| 34-1500-XXX | C0403 | C4054 | 34-1500-013 | 88-0000 |
Related videos on Youtube
Author by
jlimited
Updated on June 08, 2022Comments
-
jlimited about 2 years
I found a nice script that dynamically creates by column names for my pivot table, but I am not getting the assigned values back into the table. Here is my starting table.
ORDER_ID DSC_NAME NAME ----------- --------------- ----------- 2 34-1500-XXX DWG_DOC 3 C0403 EQIP_1 4 C4054 EQIP_2 1 34-1500-013 PART 0 88-0000 PRCS
I run this SQL to generate my columns that I want in my pivot table
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); select @cols = STUFF((SELECT distinct ',' + QUOTENAME(NAME) FROM test FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'')
This gives me the following output
[DWG_DOC],[EQIP_1],[EQIP_2],[PART],[PRCS]
When I run the dynamic SQL for the Pivot Table
SET @query = 'SELECT ' + @cols + ' from ( SELECT ORDER_ID,DSC_NAME FROM test ) x pivot ( MIN(ORDER_ID) for DSC_NAME in (' + @cols + ') ) p ' execute(@query)
I see this result...
DWG_DOC EQIP_1 EQIP_2 PART PRCS ----------- ----------- ----------- ----------- ----------- NULL NULL NULL NULL NULL
I have tried several different options, but I not come up with a solution to why this is not working.
Desired Output would be where the column order is correct by the ORDER_ID
PRCS PART DWG_DOC EQIP_1 EQIP_2 ---------- -------------- -------------- --------- --------- 88-0000 34-1500-013 34-1500-XXX C0403 C4054
But this would also work my application as well.
DWG_DOC EQIP_1 EQIP_2 PART PRCS -------------- --------- --------- -------------- ----------- 34-1500-XXX C0403 C4054 34-1500-013 88-0000