CODEWITHSUNDEEP
sql-serversql-server-2008-r2pivot
MAK
MAK

Reputation: 7260

Column specified multiple times in dynamic pivot table

I have the following table with some records in it.

Example:

Table: ffortest

create table ffortest
(
col1 int,
col2 int
)

Insertion of records:

insert into ffortest values(1,2);
insert into ffortest values(3,4);
insert into ffortest values(5,6);
insert into ffortest values(7,8);
insert into ffortest values(9,2);
insert into ffortest values(1,2);

Pivot Table Query:

DECLARE @StuffColumn varchar(max)
DECLARE @sql varchar(max)

SELECT @StuffColumn = STUFF((SELECT ','+QUOTENAME(col1)
                        FROM ffortest
          FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')
PRINT(@StuffColumn)        

SET @SQL = ' select col2,'+ @StuffColumn +'
         from
         (
            select   col1,col2
            from ffortest
         )x
         pivot
         (
             count(col1)
             for col1 in( '+@StuffColumn +')
         )p'
PRINT(@SQL)
EXEC(@SQL)

Error: column '1' specified multiple time in p.

Expected Result is:

col2  1  9  3  5  7
-------------------
2     2  1  0  0  0
4     0  0  1  0  0
6     0  0  0  1  0
8     0  0  0  0  1

Upvotes: 1

Views: 8889

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93734

While generating column list use distinct to avoid this error. Because you cannot use same column multiple times in Pivot like

    pivot
     (
         count(col1)
         for col1 in ([1],[3],[5],[7],[9],[1])
     )p'

So change your @StuffColumn like this.

SELECT @StuffColumn = STUFF((SELECT distinct ','+QUOTENAME(col1)
                        FROM ffortest
          FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') 
    ,1,1,'')

Upvotes: 1

Related Questions