Dynamic Pivot Error in sql server

Question

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 convert rows to columns in sql server

Here is my table :

TEST

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

Here's my try

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;

Expected Result :

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

@Bluefeet This was the one I wanted help on. – WiredTheories Dec 26 '14 at 11:48 — WiredTheories, Dec 26 '14 at 11:48

Pரதீப் · Accepted Answer · 2014-12-26T16:32:20.417

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((SELECT DISTINCT ',' + Quotename([check])
                      FROM   TEST
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

Or

SELECT @cols = Stuff((SELECT ',' + Quotename([check])
                      FROM   TEST
                      GROUP  BY [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

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

edited Dec 26 '14 at 16:32

answered Dec 26 '14 at 11:56

Pரதீப்

91,748
19
131
172

SELECT @cols = Stuff((SELECT ',' + Quotename([check]) FROM TEST GROUP BY [Check] , [ID] ORDER BY [ID] FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '') – WiredTheories Dec 26 '14 at 12:04
@Lewan - Mybad forgot remove Id from order by, updated check now – Pரதீப் Dec 26 '14 at 12:20
It did the trick, Is there way I could do the same task in a view. This is dynamic sql I know it can't be used in a view. But is there an alternative way to do the same task – WiredTheories Dec 26 '14 at 12:26
@Lewan - If know values in `Check` column then change it to static pivot – Pரதீப் Dec 26 '14 at 12:28
Yes that could be one way but then I will have columns with null values which I don't want . – WiredTheories Dec 26 '14 at 12:31
@Lewan - You are talking about Which column – Pரதீப் Dec 26 '14 at 12:32
The "Check" Column. The values are loaded based on business check n it keeps changing. If I do it in a static way then I would have the columns transposed with null values if they are not in the Test table. – WiredTheories Dec 26 '14 at 12:49
@Lewan - Then you can change this Dynamic Pivot query to `SP` – Pரதீப் Dec 26 '14 at 14:15
Could I use the same query to SP where I could load the results to a table ? – WiredTheories Dec 26 '14 at 14:30
1

@Lewan Yes you can check this out http://stackoverflow.com/questions/653714/insert-results-of-a-stored-procedure-into-a-temporary-table – Pரதீப் Dec 26 '14 at 14:32
I need to load the contents into a table. So I could use Insert Table Execute Resulttable. But then since this is monthly task. The table must either emptied or dropped and recreated. – WiredTheories Dec 26 '14 at 14:49
@Lewan Use `If exists` to drop and recreate table and i doubt that you can use `Insert Table Execute Resulttable` method because you are unaware of the result table column list since it is Dynamic Pivot. I would Suggest you to use `OpenRowset method` – Pரதீப் Dec 26 '14 at 14:53
**OpenRowset** is something new for me. Can I use the code in this way. 'CREATE PROC TESTRESULTS AS BEGIN SELECT * FROM sys.databases END GO sp_configure 'Show Advanced Options', 1 GO RECONFIGURE GO sp_configure 'Ad Hoc Distributed Queries', 1 GO RECONFIGURE GO SELECT * INTO #MyTable FROM OPENROWSET('SQLNCLI', 'Server=(local)\SQL2012;Trusted_Connection=yes;', 'EXEC MYPIVOT')' – WiredTheories Dec 26 '14 at 15:05
@Lewan - Updated my answer try that – Pரதீப் Dec 26 '14 at 15:20
**OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "Login timeout expired". OLE DB provider "SQLNCLI11" for linked server "(null)" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.". Msg -1, Level 16, State 1, Line 0 SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].** – WiredTheories Dec 26 '14 at 15:27
My Server is 2012 , should there be any changes in the connection property. – WiredTheories Dec 26 '14 at 15:27
@Lewan - even am not sure about the error. Give me sometime – Pரதீப் Dec 26 '14 at 16:06
@Lewan replace ` (local)\SQL2012` with `select @@SERVERNAME` result and check my answer – Pரதீப் Dec 26 '14 at 16:32
1

@Lewan replace `(local)\SQL2012` with `select @@SERVERNAME` result and change `exec DynamicPivotProcedure` to `exec databasename.schemaname.DynamicPivotProcedure` – Pரதீப் Dec 26 '14 at 16:51
I tried as you said , I still get the same error but the last part of the error says this **"Msg 53, Level 16, State 1, Line 0 Named Pipes Provider: Could not open a connection to SQL Server [53]. "** – WiredTheories Dec 26 '14 at 16:58
@Lewan - ask this as new question am sure there is something wrong with your connection string on openrowset for me it is working – Pரதீப் Dec 26 '14 at 17:12

Saravana Kumar · Answer 2 · 2014-12-26T12:16:23.083

1

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

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

edited Dec 26 '14 at 12:16

answered Dec 26 '14 at 12:06

Saravana Kumar

3,669
5
15
35

This works but I miss the ID and TotalOfScore column , – WiredTheories Dec 26 '14 at 12:13
@Lewan: Add the columns in select list. Use the updated answer. – Saravana Kumar Dec 26 '14 at 12:17
Perfect. 'I was assuming ' + cols + N' does the trick since the sub select query has the columns mentioned in it. I know this can not be used in a view. But is there an alternative to doing the same task in a view. Since dynamic sql is not often recommended. – WiredTheories Dec 26 '14 at 12:22

Dynamic Pivot Error in sql server

2 Answers2

Linked