Pivot Command, can the IN clause be dynamic rather than hard-coded values?
I want to have a number of sql statements that uses the PIVOT command be able to run dynamically without having to hard-code the values I want selected within the 'IN' clause. Here is a simple example.
REGION_ID SALES_MONTH NUM_SALES
1 02/01/2010 12
1 03/01/2010 11
1 04/01/2010 10
2 02/01/2010 22
2 03/01/2010 21
2 04/01/2010 20
The PIVOT command I used is as follows:
select * from
(select * from sales)
PIVOT
(
sum(num_sales)
for sales_month in ('02/01/2010','03/01/2010','04/01/2010'))
This works fine for now, but as new months are created, I don't want to have to redo all my sql statements each month. Any suggestions on how I can do this?