未知列数的 SQL Server 动态数据透视

2022-01-22 00:00:00 pivot sql sql-server

之前有人问过这个问题，但在一个稍微不同的情况下(似乎不适合我的问题)所以..

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

我的数据看起来像这样

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
..?   |..?        |..?

我想通过名称来获取

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

我已经关注了类似的例子在 SQL Server 中使用Pivot"将行转换为列但我离解决方案还很远……有人可以帮帮我吗?谢谢！

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!

实际代码

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

推荐答案

试试这个，它跟这里提到的一样:在 SQL Server 中使用 'Pivot' 将行转换为列

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

相关文章