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?

mohan111 · Accepted Answer · 2015-06-19T16:19:34.767

0

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

edited Jun 19 '15 at 16:19

answered Jun 19 '15 at 15:49

mohan111

8,633
4
28
55

Thanks but it doesn't help me much because, like I wrote, the values of "name" column (A, B, C, D) are dynamic and I don't know what they are. It has to be dynamic in some way – user2822455 Jun 19 '15 at 16:01
still doesn't help me much.. the values of "name" column are dynamic.. I just figured out the answer. I'll post it in my question – user2822455 Jun 19 '15 at 16:15
please see the updated answer with dynamic query @user2822455 – mohan111 Jun 19 '15 at 16:20
that one is better. @statement is still not dynamic - see my answer.. and thanks for helping :) – user2822455 Jun 19 '15 at 16:27
welcome as long as your problem solved i gave you three ways how to achieve in tabular format (using max cases,Normal pivot and Dynamic ) @user2822455 – mohan111 Jun 19 '15 at 16:30

sql server - transforming data with dynamic pivot

1 Answers1