What SQL code is best for the following column to row transformation?

Question

 Similar to the following:

Columns to Rows in MS Access

I want to know how , within the MS-Access Query Design environment, I can transform the following sample data from state #1 to state #2.

Here is what the data currently look like in my table (state #1):

Row |  School | LocationCode2011 |  LocationCode2012 | LocationCode2013 

001      ABC        1000A                1000A                2000X
002      DEF        1000A                1000A                2000X
003      GHI        2000X                1000A                2000X

Here is what I want my resulting query to look like (state #2):

Row |  LocationCode | Year | School#1 |  School#2 | School#3 

001      1000A        2011      ABC        DEF         
002      1000A        2012      ABC        DEF         GHI
003      2000X        2011      GHI
004      2000X        2012                                        
005      2000X        2013      ABC        DEF         GHI

Edit (2/19/2014): I wanted to present a simpler version (as recommended by elc below), since my previous sample data presented too many problems at once.

State #1

Row |  School | LocationCode | Year | 

001      ABC        1000A      2011               
002      DEF        1000A      2011                
003      GHI        2000X      2011 

State #2               

Row |  LocationCode | Year | School#1 |  School#2 | School#3 

001      1000A        2011      ABC        DEF         
002      2000X        2011      GHI

Please keep in mind that:

1) I am using Access 2010

Answer 1

Starting with our data in a table named [CurrentData]

Row  School  LocationCode  Year
---  ------  ------------  ----
001  ABC     1000A         2011
002  DEF     1000A         2011
003  GHI     1000X         2011

the query

SELECT 
    cd1.Year,
    cd1.LocationCode,
    cd1.School,
    COUNT(*) AS SchoolRank
FROM
    CurrentData AS cd1
    INNER JOIN
    CurrentData AS cd2
        ON cd2.Year = cd1.Year
            AND cd2.LocationCode = cd1.LocationCode
            AND cd2.School <= cd1.School
GROUP BY
    cd1.Year,
    cd1.LocationCode,
    cd1.School

produces

Year  LocationCode  School  SchoolRank
----  ------------  ------  ----------
2011  1000A         ABC              1
2011  1000A         DEF              2
2011  1000X         GHI              1

A very minor tweak to that converts the rank number to a string like "School_1"

SELECT 
    cd1.Year,
    cd1.LocationCode,
    cd1.School,
    'School_' & COUNT(*) AS XtabColumn
FROM
    CurrentData AS cd1
    INNER JOIN
    CurrentData AS cd2
        ON cd2.Year = cd1.Year
            AND cd2.LocationCode = cd1.LocationCode
            AND cd2.School <= cd1.School
GROUP BY
    cd1.Year,
    cd1.LocationCode,
    cd1.School

producing

Year  LocationCode  School  XtabColumn
----  ------------  ------  ----------
2011  1000A         ABC     School_1  
2011  1000A         DEF     School_2  
2011  1000X         GHI     School_1  

We can just wrap that in the code to produce a crosstab query

TRANSFORM First(School) AS whatever
SELECT [Year], LocationCode
FROM
    (
        SELECT 
            cd1.Year,
            cd1.LocationCode,
            cd1.School,
            'School_' & COUNT(*) AS XtabColumn
        FROM
            CurrentData AS cd1
            INNER JOIN
            CurrentData AS cd2
                ON cd2.Year = cd1.Year
                    AND cd2.LocationCode = cd1.LocationCode
                    AND cd2.School <= cd1.School
        GROUP BY
            cd1.Year,
            cd1.LocationCode,
            cd1.School
    ) AS something
GROUP BY [Year], LocationCode
PIVOT XtabColumn

and we get

Year  LocationCode  School_1  School_2
----  ------------  --------  --------
2011  1000A         ABC       DEF     
2011  1000X         GHI               

Answer 2

I think what you'll want to try for this is the UNPIVOT operation. UNPIVOT (LocationCode FOR Locations IN (Locationcode2011, LocationCode2012, LocationCode2013)

I found a short tutorial that demonstrates how to do a very similar problem if you're unsure how to use UNPIVOT.

Answer 3

You've got a lot going on there. If this is a one time thing to be done on the currently existing data you probably could manage something with a separate crosstab for each year and then union those crosstabs together (might need to do maketables from the crosstabs as an intermediary step if Access chokes).

If this is something where you are going to keep adding years and requesting this operation--you are better of reorganizing your data into a more usable format. Burying information you want to use dynamically in fieldnames is a very bad idea. If it's possible at all it will be slow and fragile. Bust down to a simple table of (row, school, year, location) and then build up from there as needed.