Tuesday, March 5, 2013

dynamic PIVOT where number of columns are dynamic in pivot columns

PIVOT used to rotate the data from one column into multiple columns.

STATIC Pivot meaning you hard code the columns that you want to rotate

For  example :


CREATE TABLE temp
(
    period_id INTEGER ,
    lease_id  INTEGER  ,
    charge_id VARCHAR(20) ,
    charge_amount MONEY
)
INSERT INTO temp
        ( period_id ,
          lease_id ,
          charge_id ,
          charge_amount
        )
VALUES  ( 100 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          12345  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2000 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          678910  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '300' , -- charge_id - varchar(20)
          78950  -- charge_amount - money
        ) ,
        ( 101 , -- period_id - integer
          2002 , -- lease_id - integer
          '310' , -- charge_id - varchar(20)
          9002  -- charge_amount - money
        )
        Select period_id,lease_id,[300] as charge_300, [310] as Charge_310  FROM(Select period_id,lease_id,charge_id,charge_amount from temp )p     pivot(sum(charge_amount)FOR charge_id in ([300],[310])) as PVT

but in case of number of columns change dynamically then you need dynamic pivot . for this you need to create series of columns using dynamic sql . 


DECLARE @columns VARCHAR(8000)

SELECT 
@columns = 
COALESCE
(
 @columns + ',[' + charge_id + ']',
 '[' + charge_id + ']'
)
FROM 
#temp 
group by charge_id

// The above query tries to create distinct charge_id as the columns
// @columns= [300],[301]

DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM
(
 SELECT 
 period_id,lease_id ,charge_id,charge_amount 
 FROM 
 #temp 
) P
PIVOT
(
 SUM(charge_amount) 
 FOR charge_id in (' + @columns + ')
) AS PVT'

 EXECUTE (@query)
 GO



Links for above sql fiddle 






No comments:

Post a Comment