Dynamically Pivoting a table

Question

I have a table structure somewhat like the following:

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
        )

Table structure

I want an output that looks like the following:

Example final output

I'm confident I could get there by creating a series of cte's and constantly referring back to the source table however I don't think that's a great solution here as I have a lot of these charge codes and new ones are expected to be added in the future (and having to update the query each time this happens would be terrible practice). I've looked into the pivot function but I don't understand how to use it, I'd like to understand how to apply it in this scenario so I can add this into my arsenal.

PIVOT works if the series of values is known upfront. In your case you need to create a dynamic SQL where the SQL is built by populating PILOT columns from the table. Search here "dynamic PILOT" and you will find a few examples — cha, Mar 06 '13 at 04:19

praveen · Accepted Answer · 2013-03-06T05:09:14.340

Try this :

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

SQLFIDDLE Demo

In case you want dynamic PIVOT where in the number of columns are not fixed then try the below query

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

score 1 · Answer 2 · edited May 23 '17 at 11:45

1

Understanding PIVOT function in T-SQL

This should help you. It talks about static and dynamic pivots. I guess, dynamic pivots would be more suitable in your current case as your columns might keep changing.

edited May 23 '17 at 11:45

Community

1
1

answered Mar 06 '13 at 05:02

navi

179
1
7

Dynamically Pivoting a table

2 Answers2