SQL Pivot function in Access2007 ... Can it be done ?

[Aussie Rules]

Hi,

I have a situation where I store data row by row, such as

ProjectID CategoryID MonthID ValueID
1 1 1 203
1 2 1 32
1 2 2 23
1 2 3 98
2 4 2 51
2 7 2 55
2 9 4 24

etc...

I need to be able to extract this data from the table, but in row format so
that the returned result set would be:

ProjectID CategoryID Month1 Month2 Month3 Month4
Month5 .
1 1 203
1 2 32
1 2 23
1 2
98

2 4 51
2 7 55
2 9
24



The catch is that each project will have a different number of months (as
each project is of a different time).. however I will only ever show one
projects information at any one time.

Is this possible to rotate and use my data in a way to get the result set I
need ?


Thanks

 

[Allen Browne]

Use a crosstab query to transform the values in the MonthID field into the
columns of the query.

ProjectID = row heading (group by)
CategoryID = row heading (group by)
MonthID = column heading (group by)
ValueID = value (first, or sum or whatever.)

For info on how to solve crosstab issues, see:
Crosstab query techniques
at:
http://allenbrowne.com/ser-67.html