dynamic PIVOT where number of columns are dynamic in pivot columns

A 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