Dynamic Pivot Error In Sql Server

I used the above code from the link. But I recieve an error as Msg 8156, Level 16, State 1, Line 14 The column 'Factory' was specified multiple times for 'p' Efficiently conve

Solution 1:

As mentioned in Error You cannot specify same column name more than once in Pivot like

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

Change your @cols initialization like this

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

Or

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

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

createprocedure dbname.schemaname.DynamicPivotProcedure 
asbeginDECLARE@colsAS NVARCHAR(MAX),
        @queryAS NVARCHAR(MAX)

SELECT@cols= Stuff((SELECT','+ Quotename([check])
                      FROM   TEST
                      GROUPBY [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') ISNOTNULLbeginDROPTABLE #MyTempTable
endSELECT*INTO #MyTempTable FROM 
OPENROWSET('SQLNCLI', 'Server=(local)\SQL2008;Trusted_Connection=yes;',
     'EXEC dbname.schemaname.DynamicPivotProcedure')

SELECT*FROM #MyTempTable

Solution 2:

Remove the Id from GROUP BY and ORDER By clause. So that you get the DISTINCT Check columns.

DECLARE@colsAS NVARCHAR(MAX),
    @queryAS NVARCHAR(MAX)
select@cols= STUFF((SELECT','+ QUOTENAME([check]) 
  from #Sample
  groupby [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 #Sample AS TEST
  ) T
  pivot 
  (
     SUM (T.[score])
      for T.[check] in ('+@cols+ N')
  ) p 'exec sp_executesql @query;