sql server - transforming data with dynamic pivot

Question

I need to dynamically transform data in table #table from this format:

spot_id        name        pct
-------        ----        ---
1               A           2
1               B           8
1               C           6
2               A           4
2               B           5
3               A           5
3               D           1
3               E           4

to:

spot_id     A     B     C     D     E
-------    ---   ---   ---   ---   ---
1           2     5     6     0     0
2           4     5     0     0     0
3           5     0     0     1     4

The thing is that I don't know in advance what the values of column "name" are or how many of them there are, so I think that I have to use some kind of dynamic SQL pivoting

---

Just figured out how to solve the problem:

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

select @cols = STUFF((SELECT ',' + QUOTENAME(name) 
                    from (SELECT DISTINCT name FROM #table) T
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = N'SELECT spot_id,' + @cols + N' from 
             (
                select spot_id, name, pct
                from #table
            ) as x
            pivot 
            (
                max(pct)
                for name in (' + @cols + N')
            ) as p '

exec sp_executesql @query;

If there more elegant way to do the same?

Answer 1

declare @t table (Spot int,name varchar(1),pct int)
insert into @t(Spot,name,pct)values(1,'A',2),(1,'B',8),(1,'C',6),(2,'A',4),(2,'B',5),(3,'A',5),(3,'D',1),(3,'E',4)


select Spot,ISNULL([A],0)[A],ISNULL([B],0)[B],ISNULL([C],0)[C],ISNULL([D],0)[D],ISNULL([E],0)[E] from (Select Spot,PCT,Name from @t)T
PIVOT(MAX(PCT) FOR Name IN ([A],[B],[C],[D],[E]))P


select Spot,ISNULL(MAX(CASE WHEN name = 'A' THEN pct END),0)A,ISNULL(MAX(CASE WHEN name = 'B' THEN pct END),0)B ,
ISNULL(MAX(CASE WHEN name = 'C' THEN pct END),0)C ,
ISNULL(MAX(CASE WHEN name = 'D' THEN pct END),0)D ,ISNULL(MAX(CASE WHEN name = 'E' THEN pct END),0)E  from @t
GROUP BY Spot

Using Dynamic Query :

if object_id('tempdb..#t') is not null
    drop table #t

CREATE  table #t(Spot int,name varchar(1),pct int)
insert into #t(Spot,name,pct)values(1,'A',2),(1,'B',8),(1,'C',6),(2,'A',4),(2,'B',5),(3,'A',5),(3,'D',1),(3,'E',4)

DECLARE @statement NVARCHAR(max)
,@columns NVARCHAR(max)

SELECT @columns = ISNULL(@columns + ', ', '') + N'[' + tbl.name + ']'
FROM (
   SELECT DISTINCT name
   FROM #t
   ) AS tbl

SELECT @statement = ' select Spot,ISNULL([A],0)[A],ISNULL([B],0)[B],ISNULL([C],0)[C],ISNULL([D],0)[D],ISNULL([E],0)[E] from (Select Spot,PCT,Name from #t)T
PIVOT(MAX(PCT) FOR Name IN (' + @columns + ')) as pvt'

EXEC sp_executesql @statement = @statement