未知列数的SQL Server动态透视

ubby3x7f 于 2023-01-29 发布在 SQL Server

关注(0)|答案(4)|浏览(151)

这个问题以前有人问过，但在一个稍微不同的场景（一个似乎不适合我的问题），所以...
我有这样的数据

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

我已经跟随像Convert Rows to columns using 'Pivot' in SQL Server的例子，但我离解决方案还很远..有人能给予我一把吗？谢谢！
编辑：实际代码

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

来源：https://stackoverflow.com/questions/52761674/sql-server-dynamic-pivot-for-an-unknow-number-of-columns

4条答案

按热度按时间

cqoc49vn1#

试试这个，它遵循这里提到的相同的例子：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

赞(0）回复(0）举报 2023-01-29

mrphzbgm2#

试试这个动态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)

赞(0）回复(0）举报 2023-01-29

sxissh063#

试试这个查询（我认为它更清楚）。我使用KashifQureshi的代码创建一个临时表，但是我的代码在PIVOT部分是不同的

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

赞(0）回复(0）举报 2023-01-29

t30tvxxf4#

Kashif给出的答案是可行的。下面是一个利用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

赞(0）回复(0）举报 2023-01-29

我来回答

未知列数的SQL Server动态透视

4条答案

相关问题

热门标签

最新问答