动态 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，并选择列name:

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) 代替 MIN(ORDER_ID).像这样:

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 演示

这会给你:

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屋！