SQL Query with Dynamic Columns Using Pivot

问题:

I know this topic is everywhere but I couldn't get it to work properly. It's probably something very simple.

Here is a sample of the data after a basic query:

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

What I would like is where the column names are dynamic:

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

Here is the code I have put together so far based on my research:

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)

When I run the query I get a syntax error.

回答1:

Using Dynamic 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)

Result

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

回答2:

Give this a shot...

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

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

--==========================================================

DECLARE 
    @PivotColumns NVARCHAR(4000),
    @sql NVARCHAR(4000),
    @DeBug BIT = 0;

SELECT 
    @PivotColumns = CONCAT(@PivotColumns, N',
    ', QUOTENAME(td.Model), N' = SUM(CASE WHEN td.Model = ', QUOTENAME(td.Model, ''''), N' THEN td.SomeCount END)') 
FROM
    #TestData td
GROUP BY
    td.Model
-- ORDER BY ??? if you want the columns in a specific ordinal position.
;

SET @sql = CONCAT(N'
SELECT 
    td.[Site]',
    @PivotColumns, N'
FROM
    #TestData td
GROUP BY
    td.[Site];');

IF @DeBug = 1
BEGIN 
    PRINT (@sql);
END;
ELSE 
BEGIN 
    EXEC sys.sp_executesql @sql;
END;

Results...

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

回答3:

From your query

PIVOT
(
    max(Model)
    FOR Site in ' + @cols + ')
) AS piv

you should use the column Count in MAX() function and Model in FOR...IN section. this is because your new values will be the Count and your new columns will be from the values in Model