Firas S Firas S - 2 months ago 21
SQL Question

how to make a dynamic sql pivot

I have a select statement with the following

select country,city,school,class,quantity from tableA


I want the pivot to be based on class like in the below table:

country City School ClassA ClassB ClassC ClassD
XXX AAA SCH01 37 37 39 37
XXX BBB SCH02 12 12 1 12
XXX BBB SCH03 6 6 9 6
XXX DDD SCH04 1 1 1 1
YYY ABC SCH05 1 1 1 1
YYY CDE SCH06 1 1 1 1
YYY EDY SCH07 1 1 1 1
YYY ZER SCH08 1 1 1 1
SSS GFY SCH09 1 1 1 1
SSS AHY SCH10 1 1 1 1

Answer

MS SQL SERVER:

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

select @cols = STUFF((SELECT ',' + QUOTENAME([class]) 
                    from [table-name]
                    group by [class]
                    order by [class]
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT [country], [city], [school],' + @cols + ' from 
             (
                select [country], [city], [school],[class],[quantity]
                from [table-name]
            ) x
            pivot 
            (
                sum([quantity])
                for [class] in (' + @cols + ')
            ) p '

execute(@query);
Comments