create pivot table, with dynamic columns
Posted on 2006-03-21
I want to create a stored procedure that takes a start month and returns a crosstab
My source table contains columnss:
centerNumber,YearMonth, and expenseAmount
The crosstab result should have 13 columns:
and yearMonth for 12 conscutive months.
The trick is that I want to be able to dynamically determine the start month based on the yearMonth value passed into the stored procedure. The new PIVOT TSQL statement makes this task easier, but I still don't know how to dynamically determine the month columns. Below I have a pivot table statement which always starts with Jan05, I'd like to be able to start with any month.
Center_num, mth01, mth02,  mth03, mth04,
 mth05,  mth06, mth07, mth08, mth09,
 mth10, mth11, mth12
(select Center_Num,Fund_Mth,sumExp from pri_pmpmNew_01_main ) t
for Fund_Mth in (,,,,,,,,,
) as pvt
Thanks really appreciate the help!