sql server - Column specified multiple times in dynamic pivot table -

i have following table records in it.

example:

table: ffortest

create table ffortest ( col1 int, col2 int ) 

insertion of records:

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

pivot table query:

declare @stuffcolumn varchar(max) declare @sql varchar(max)  select @stuffcolumn = stuff((select ','+quotename(col1)                         ffortest           xml path(''), type         ).value('.', 'nvarchar(max)')      ,1,1,'') print(@stuffcolumn)          set @sql = ' select col2,'+ @stuffcolumn +'                   (             select   col1,col2             ffortest          )x          pivot          (              count(col1)              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 

while generating column list use distinct avoid error. because cannot use same column multiple times in pivot like

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

so change @stuffcolumn this.

select @stuffcolumn = stuff((select distinct ','+quotename(col1)                         ffortest           xml path(''), type         ).value('.', 'nvarchar(max)')      ,1,1,'')