动态SQL Server数据透视表 [英] Dynamic SQL Server Pivot Table
问题描述
我发现一个漂亮的脚本,通过我的数据透视表的列名动态创建,但是我没有将指定的值返回到表中。这是我的起始表。
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
我跑这个SQL在我的数据透视表中生成我需要的列
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,'')
这给我以下输出
[DWG_DOC],[EQIP_1],[EQIP_2],[PART],[PRCS]
当我为数据透视表运行动态SQL
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.
期望的输出将其中列顺序由ORDER_ID正确
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
推荐答案
从选择中删除 ORDER_ID
,然后选择列名称
:
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 '
并使用 MAX(DSC_Name) code>而不是
MIN(ORDER_ID)
。像这样:
SET @query = 'SELECT '+ @cols + ' from
(
SELECT DSC_NAME, Name
FROM test
) x
pivot
(
MAX(DSC_Name)
for NAME in (' + @cols + ')
) p ';
SQL Fiddle Demo
这将给你:
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 |
这篇关于动态SQL Server数据透视表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!