使用数据透视表的动态列的SQL查询

Question

我知道这个话题无处不在，但我无法使其正常运行。这可能很简单。

这是基本查询后的数据示例：

-----------------------------------
|Site| Model              | Count |
-----------------------------------
AAA   ProLiant DL380 G7     1
AAA   OptiPlex 790          500
BBB   OptiPlex 780          80
CCC   OptiPlex 790          23
...

我想要的是列名称是动态的：

--------------------------------------------------------
|Site| ProLiant DL380 G7 | OptiPlex 790 | OptiPlex 780 |...
--------------------------------------------------------
AAA             1               500            0
BBB             0               0              80
CCC             0               23             0

到目前为止，根据我的研究，这是我整理的代码：

DECLARE 
    @cols AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Model)
     FROM
    (
        SELECT IIF(COMP.Manufacturer0 LIKE '%lenovo%',PRD.Version0,COMP.Model0) AS Model
        FROM v_GS_COMPUTER_SYSTEM COMP
        JOIN v_GS_COMPUTER_SYSTEM_PRODUCT PRD ON COMP.ResourceID=PRD.ResourceID
    ) AS inner_tbl
    FOR XML PATH(''),Type).value('.', 'NVARCHAR(MAX)'),1,1,'')

SET @query = 'SELECT Site, ' + @cols + ' FROM
    (
        SELECT Site, Model, COUNT(Model) AS Count FROM
        (
            SELECT UPPER(Substring(SYS.Name0,2,3)) AS Site, IIF(COMP.Manufacturer0 LIKE ''%lenovo%'',PRD.Version0,COMP.Model0) AS Model
            FROM v_GS_System SYS
            JOIN v_GS_COMPUTER_SYSTEM COMP ON SYS.ResourceID=COMP.ResourceID
            JOIN v_GS_COMPUTER_SYSTEM_PRODUCT PRD ON SYS.ResourceID=PRD.ResourceID
        ) AS inner_tbl2
        GROUP BY Site, Model
    ) AS inner_tbl1
    PIVOT
    (
        max(Model)
        FOR Site in ' + @cols + ')
    ) AS piv

'

EXECUTE(@query)

当我运行查询时，出现语法错误。

Answer 1

使用动态SQL

IF OBJECT_ID('tempdb..#TempData', 'U') IS NOT NULL 
DROP TABLE #TempData;

CREATE TABLE #TempData (
    [Site] CHAR(3) NOT NULL,
    Model VARCHAR(30) NOT NULL,
    SomeCount INT NOT NULL DEFAULT(0)
    );
INSERT #TempData (Site, Model, SomeCount) VALUES
    ('AAA', 'ProLiant DL380 G7', 1), 
    ('AAA', 'OptiPlex 790', 500), 
    ('BBB', 'OptiPlex 780', 80),
    ('CCC', 'OptiPlex 790', 23);

Declare @DynamicCol nvarchar(max),@DynamicColNull nvarchar(max)
        ,@Sql nvarchar(max)

SELECT @DynamicColNull=STUFF((SELECT DISTINCT ', '+'ISNULL('+QUOTENAME(Model),','+'''0'''+') As '+QUOTENAME(Model)
                        FROM #TempData FOR XML PATH ('')),1,2,'')

SELECT @DynamicCol=STUFF((SELECT DISTINCT ', '+QUOTENAME(Model) FROM #TempData FOR XML PATH ('')),1,2,'')

SET @Sql='SELECT [Site], '+@DynamicColNull+' From
            (   
            SELECT * from #TempData
            )
            AS Src
            PIVOT
            (
            MAX(SomeCount) FOR [Model] IN ('+@DynamicCol+')
            )AS Pvt'
PRINT @Sql
EXEC(@Sql)

结果

Site    OptiPlex 780    OptiPlex 790    ProLiant DL380 G7
AAA         0               500             1
BBB         80              0               0
CCC         0               23              0