Curtis MacD Curtis MacD - 3 months ago 10
SQL Question

Vertical to Horizontal Table with Month Grouping

Long time lurker - first time poster.

I'm new to Access and have a data table I'm trying to manipulate via a query.

Current View:



ID | $Value | Month
1 | 184 | 10/1/2012
1 | 186 | 11/1/2012
1 | 176 | 12/1/2012
1 | 183 | 1/1/2013
1 | 192 | 2/1/2013
1 | 201 | 3/1/2013
1 | 183 | 4/1/2013
1 | 179 | 5/1/2013
1 | 177 | 6/1/2013
1 | 135 | 7/1/2013
1 | 202 | 8/1/2013
1 | 188 | 9/1/2013
2 | 258 | 5/1/2013
2 | 126 | 6/1/2013
2 | 236 | 7/1/2013
2 | 367 | 8/1/2013
2 | 450 | 9/1/2013
2 | 186 | 10/1/2013
2 | 248 | 11/1/2013
2 | 264 | 12/1/2013
2 | 257 | 1/1/2014
2 | 264 | 2/1/2014
2 | 138 | 3/1/2014
2 | 264 | 4/1/2014


Desired Ending View:

ID | Month 1 | Month 2 | Month 3 | Month 4 | Month 5 | ... | Month 12
1 | 184 | 186 | 176 | 183 | 192 | ... | 188
2 | 258 | 126 | 236 | 367 | 450 | ... | 264


I'm attempting to transpose the data in Access so that there is only one row per ID and group the months (always labelled 'Month 1', 'Month 2', etc...). I only want to see 12 columns of months (Not one for each month and year). So in the above example ID 1, Month 1 is 184 and ID 2, Month 1 is 258, despite the month and year of each ID being different.

Any guidance/help is greatly appreciated.

Answer

As has been suggested - a crosstab (pivot) query would work here:

TRANSFORM   Sum(sValue) AS SumOfsValue
SELECT      ID
FROM        Table1
GROUP       BY ID
PIVOT       "Mo" & Month([dMonth])

Is your 'Desired Ending View' correct?
e.g. You've got 188 for ID 1 in month 12 - shouldn't this be 176?