Ramdeo angh Ramdeo angh - 3 months ago 11
SQL Question

Convert row to column in SQL Server does not work?

period providerid type volume subscribers
--------------------------------------------------
Aug-2016 7 1 4027917 117172
Aug-2016 7 2 5325430 232293
Aug-2016 7 3 8722165 236472
Jul-2016 7 1 2981655 97409
Jul-2016 7 2 6449570 147315
Jul-2016 7 3 7702484 206140


I want my result in this format.

period providerid SMS Data minutes
Aug-2016 7 432142 42342 5454
Jul-2016 7 5454 5454 545


I have tried this query but it does not work.

select
period, providerid, 1 as SMS, 2 as Data, 3 as minutes
from
#P
pivot
(sum(volume) for [type] in ([1],[2],[3])) as P


Please help me in SQL server

Answer

Get rid of subscribers column:

SELECT  [period],
        providerid, 
        [1] as SMS, 
        [2] as [Data], 
        [3] as [minutes]
FROM (
    SELECT [period],providerid, [type], volume
    FROM YourTable         
) as t
PIVOT (
    MAX(volume) FOR [type] in ([1], [2], [3]) 
) as P

Output:

period      providerid  SMS     Data    minutes
Aug-2016    7           4027917 5325430 8722165
Jul-2016    7           2981655 6449570 7702484