SQL Server Dynamic pivot for an unknow number of columns

Question

The question has been asked before, but in a slightly different scenario (one that doesn't seem to fit to my question) so..

I have data that looks like this

Name  |Item       |Note
George|Paperclip  |Two boxes
George|Stapler    |blue one
George|Stapler    |red one
George|Desk lamp  |No light bulb
Mark  |Paperclip  |One box 2"
Mark  |Paperclip  |One box 4"
Mark  |Block Notes|a blue one
..?   |..?        |..?

And I would want to pivot by name, to obtain

Name  |Paperclip|Stapler|Desk Lamp|Block Notes
George|        1|      2|        1| NULL
Mark  |        2| NULL  | NULL    |          1

I've follower the examples like Convert Rows to columns using 'Pivot' in SQL Server but I'm far from a solution.. can someone please give me an hand? Thanks!

edit: the actual code

drop table #temp2
SELECT DISTINCT *,
CASE WHEN Item IS NULL THEN NULL ELSE COUNT(Item) OVER(PARTITION BY Name) END CNT 
    INTO #TEMP2
    FROM [ISPBIGFIX].[dbo].[C_INV_ErroriTavolette_v11]

DECLARE @cols NVARCHAR (MAX)
DECLARE @Columns2 NVARCHAR (MAX)

SET @cols = SUBSTRING((SELECT DISTINCT ',['+Item+']' FROM #TEMP2 GROUP BY Item FOR XML PATH('')),2,8000)

SET @Columns2 = SUBSTRING((SELECT DISTINCT ',ISNULL(['+Item+'],0) AS ['+Item+']' FROM #TEMP2 GROUP BY Item FOR XML PATH('')),2,8000)


DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT Name,' + @Columns2 + ' FROM 
             (
                 SELECT Name,ErrorType,CNT FROM #TEMP2
             ) x
             PIVOT  
             (
                 SUM(CNT)
                 FOR [Item] IN (' + @cols + ')
            ) p
            WHERE Name IS NOT NULL;'



EXEC SP_EXECUTESQL @query

added the code, but the result is not what i'm looking for of course — Aldo, Oct 11 '18 at 14:07
Instead of using a query that selects from your table could you post ddl for #TEMP2 and then some insert statements so we can reproduce this? — Sean Lange, Oct 11 '18 at 14:08
Seems that some others already put it together. But ddl = [data definition language](https://en.wikipedia.org/wiki/Data_definition_language). In other words the create table statements. — Sean Lange, Oct 11 '18 at 14:21
https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ — Aaron Bertrand, Oct 11 '18 at 14:51

score 2 · Answer 1 · answered Oct 11 '18 at 14:09

Try This Dynamic Sql

IF OBJECT_ID('dbo.TT')IS NOT NULL
DROP TABLE TT
;WITH CTE(Name  ,Item ,Note)
AS
(
SELECT 'George','Paperclip'  ,'Two boxes'     UNION ALL
SELECT 'George','Stapler'    ,'blue one'      UNION ALL
SELECT 'George','Stapler'    ,'red one'       UNION ALL
SELECT 'George','Desk lamp'  ,'No light bulb' UNION ALL
SELECT 'Mark'  ,'Paperclip'  ,'One box 2'     UNION ALL
SELECT 'Mark'  ,'Paperclip'  ,'One box 4'     UNION ALL
SELECT 'Mark'  ,'Block Notes','a blue one'
)
SELECT *,CASE WHEN Item IS NOT NULL THEN 1 ELSE 0 END AS Item2 INTO TT FROM CTE

SELECT * FROM TT

DECLARE @Sql nvarchar(max),
        @Sqlcol  nvarchar(max),
        @ISNULLSqlcol nvarchar(max)

SELECT  @Sqlcol=STUFF((SELECT  DISTINCT  ', '+QUOTENAME(Item) 
                FROM TT  FOR XML PATH ('')),1,1,'')

SELECT  @ISNULLSqlcol=STUFF((SELECT DISTINCT  ', '+'ISNULL(SUM('+QUOTENAME(Item) +'),''0'') AS '+QUOTENAME(Item)
                FROM TT  FOR XML PATH ('')),1,1,'')

SET @Sql='SELECT Name,'+@ISNULLSqlcol+'FROM 
         (
          SELECT * FROM TT
          ) AS SRc
          PIVOT
          (
          SUM(Item2) FOR Item IN('+@Sqlcol+')
          ) AS Pvt GROUP BY Name'

Print @Sql
EXEC (@Sql)

Kashif Qureshi · Accepted Answer · 2018-10-11T15:05:32.573

Try this, It follows the same example mentioned here:Convert Rows to columns using 'Pivot' in SQL Server

--Drop Sample temp Table     

    IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    BEGIN
        DROP TABLE #temp2
    END

--create Sample temp Table 

    create Table #temp2
    (
    [name] varchar(255),
    Item varchar(255),
    note varchar(255)
    )

--Insert Sample Data

    insert into #temp2
    values( 'George','Paperclip','Two boxes'),
    ('George','Stapler','blue one'),
    ('George','Stapler','red one'),
    ('George','Desk lamp','No light bulb'),
    ('Mark','Paperclip','One box 2'),
    ('Mark','Paperclip','One box 4'),
    ('Mark','Block Notes','a blue one')

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

--Generate Columns from Data
--Generate Columns from Data

select @cols = STUFF((SELECT ', isnull(' + QUOTENAME(Item)  + ',0) as' +  QUOTENAME(Item)
                    from #temp2
                    group by Item
                    order by Item
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

select @cols2 = STUFF((SELECT ', ' + QUOTENAME(Item)  
                    from #temp2
                    group by Item
                    order by Item
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')


--Pivot Query
    set @query = 'SELECT [name],' + @cols + ' from 
                 (
                      select [Name], Item, count(*) as xcount
                   from #temp2
                   group by  Name, Item
                ) x
                pivot 
                (
                    sum(xCount)
                    for Item in (' + @cols2+ ')
                ) p '

    execute(@query);

--Drop Sample Temp Table

    IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    BEGIN
        DROP TABLE #temp2
    END

Very fast, does not depend on a Temporary table.. truly wonderful, thanks! but what if 0 (zero) is needed instead of Null? — Aldo, Oct 11 '18 at 14:46
Works wonderfully, thanks! Just to understand it better.. is the isnull(' + QUOTENAME(ErrorType) + ',0) that does it? (0 instead of nulls?) — Aldo, Oct 11 '18 at 15:42
isnull() is the function that replaces NULL with the specified replacement value. Here is the documentation from microsoft on it: https://learn.microsoft.com/en-us/sql/t-sql/functions/isnull-transact-sql?view=sql-server-2017 — Kashif Qureshi, Oct 11 '18 at 16:30
Thanks for the amazing work @KashifQureshi. I just had an add-on question to this. what if we need to insert the output into a temp table? — funnyguy, Sep 29 '22 at 08:03

score 0 · Answer 3 · answered Oct 17 '18 at 16:16

Try this query (I think its much more clearly). I use the code of Kashif Qureshi to create a temporary table, but my code is different in PIVOT part

--Drop Sample temp Table     

    IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    BEGIN
        DROP TABLE #temp2
    END

--create Sample temp Table 

    create Table #temp2
    (
    [name] varchar(255),
    Item varchar(255),
    note varchar(255)
    )

--Insert Sample Data

    insert into #temp2
    values( 'George','Paperclip','Two boxes'),
    ('George','Stapler','blue one'),
    ('George','Stapler','red one'),
    ('George','Desk lamp','No light bulb'),
    ('Mark','Paperclip','One box 2'),
    ('Mark','Paperclip','One box 4'),
    ('Mark','Block Notes','a blue one')

    --- PIVOT
    DECLARE @v_query  VARCHAR(8000) -- main query
    DECLARE @v_columns VARCHAR(8000) -- columns

    SET @v_columns =''

    -- Get string columns
    SELECT @v_columns += '[' + CONVERT(VARCHAR, Item) +'],' FROM (SELECT DISTINCT Item FROM #temp2) AS temp

    -- Delete the last comma
    SET @v_columns = LEFT(@v_columns,LEN(@v_columns)-1)


    -- Main query

    SET @v_query = 'SELECT Name, ' + @v_columns +' FROM
                    (
                        SELECT Name, Item FROM #temp2
                    ) T             
                    PIVOT
                    (
                        Count(Item)
                        FOR Item IN ('+ @v_columns +')
                    ) PVT'
    EXEC (@v_query)

-- DROP
    IF OBJECT_ID('tempdb..#temp2') IS NOT NULL
    BEGIN
        DROP TABLE #temp2
    END

Tim - BrydensBI · Answer 4 · 2023-01-29T00:33:27.593

The answer by Kashif works. Here is a version that leverages STRING_AGG

--Drop Sample temp Table     
DROP TABLE IF EXISTS #temp2

--Create Sample temp Table 
CREATE Table #temp2     (
    [name] varchar(255),
    Item varchar(255),
    note varchar(255)
)

--Insert Sample Data
INSERT INTO #temp2
VALUES ( 'George','Paperclip','Two boxes'),
('George','Stapler','blue one'),
('George','Stapler','red one'),
('George','Desk lamp','No light bulb'),
('Mark','Paperclip','One box 2'),
('Mark','Paperclip','One box 4'),
('Mark','Block Notes','a blue one')

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

--Generate Columns from Data
SET @cols = (SELECT STRING_AGG (' isnull(' + QUOTENAME(Item)  + ',0) as' +  QUOTENAME(Item), ',') as colR
FROM (SELECT ITEM FROM (SELECT ITEM FROM #temp2 GROUP BY ITEM) x GROUP BY ITEM) Y )
               
SET @cols2 = (SELECT STRING_AGG (QUOTENAME(Item), ',') as colR
FROM (SELECT ITEM FROM (SELECT ITEM FROM #temp2 GROUP BY ITEM) x GROUP BY ITEM) Y )
               
SET @query = 'SELECT [name],' + @cols + ' 
            FROM (
                SELECT [Name], Item, COUNT(*) as xcount
                FROM #temp2
                GROUP BY Name, Item
            ) x
            PIVOT (
                SUM(xCount)
                FOR Item IN (' + @cols2+ ')
            ) p '

EXECUTE (@query);

--Drop Sample Temp Table
DROP TABLE IF EXISTS #temp2

SQL Server Dynamic pivot for an unknow number of columns

4 Answers4

Linked

Related