SQL Query with Dynamic Columns Using Pivot

Question

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.

Answer 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