SQL Server中的动态数据透视错误 [英] Dynamic Pivot Error in sql server

问题描述

我从链接中使用了上面的代码.但是我收到一个错误

I used the above code from the link. But I recieve an error as

消息8156，级别16，状态1，第14行工厂"列为 为"p"指定了多次

Msg 8156, Level 16, State 1, Line 14 The column 'Factory' was specified multiple times for 'p'

在SQL Server中有效地将行转换为列

这是我的桌子:

测试

ID      score    Check      TotalofScore
------  -----    -------    ------------
867439  1        factory        1
867439  1        Plant          1
867442  1        factory        1
867442  1        Plant          1
923991  1        Warehouse      1
923991  1        Plant          1
923930  1        factory        1
923930  1        Plant          1
923101  1        Warehouse      1
923101  1        Plant          1

这是我的尝试

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

SELECT @cols = Stuff((SELECT ',' + Quotename([check])
                      FROM   TEST
                      GROUP  BY [Check],
                                [ID]
                      ORDER  BY [ID]
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT ' + @cols + N' from 
             (SELECT TEST.[ID],
                     Score,
                     [check],
                     [Total Of Score] = Count(TEST.Score) over(partition by [ID], [score], [check])
             FROM TEST) T
            pivot 
            (
             SUM (T.[score])
             for T.[check] in (' + @cols + N') ) p '

EXEC Sp_executesql @query; 

预期结果:

ID       TotalofScore   factory     Plant   Warehouse
------   ------------   -------     -----   ---------
867439      1             1 
867439      1                                   1
867442      1                       1
867442      1                                   1
923991      1             1 
923991      1             1
923930      1                       1
923930      1                       1
923101      1              1
923101      1                                   1

推荐答案

如错误所述，您不能像

..pivot (SUM (T.[score]) for T.[check] in ([factory,[Plant],[factory]..))p

..pivot (SUM (T.[score]) for T.[check] in ([factory,[Plant],[factory]..))p

像这样更改您的@cols初始化

SELECT @cols = Stuff((SELECT DISTINCT ',' + Quotename([check])
                      FROM   TEST
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 

或

SELECT @cols = Stuff((SELECT ',' + Quotename([check])
                      FROM   TEST
                      GROUP  BY [Check]
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') 

更新:要将Dynamic Pivot转换为过程并将结果插入到新表中

Update : To convert the Dynamic Pivot into procedure and insert the result into new table

create procedure dbname.schemaname.DynamicPivotProcedure 
as
begin
DECLARE @cols  AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX)

SELECT @cols = Stuff((SELECT ',' + Quotename([check])
                      FROM   TEST
                      GROUP  BY [Check]
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = N'SELECT ID, [Total Of Score],' + @cols + N' from 
             (SELECT TEST.[ID],
                     Score,
                     [check],
                     [Total Of Score] = Count(TEST.Score) over(partition by [ID], [score], [check])
             FROM TEST) T
            pivot 
            (
             SUM (T.[score])
             for T.[check] in (' + @cols + N') ) p '

EXEC Sp_executesql @query; 
end

go
sp_configure 'Show Advanced Options', 1
GO
RECONFIGURE
GO
sp_configure 'Ad Hoc Distributed Queries', 1
GO
RECONFIGURE
GO

IF OBJECT_ID('tempdb..#MyTempTable') IS NOT NULL 
begin
DROP TABLE #MyTempTable
end

SELECT * INTO #MyTempTable FROM 
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC dbname.schemaname.DynamicPivotProcedure')

SELECT * FROM #MyTempTable

这篇关于SQL Server中的动态数据透视错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！