yogi_Rearrange Data In Table A6:E27 As Specified (as shown in G6:K15)

                                          Google Spreadsheet   Post  #1084
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.    Mar 18, 2013
user Victor DiGiovanni :(http://productforums.google.com/forum/?zx=9bydteme3h15#!category-topic/docs/spreadsheets/9M3gCdDnp9U)
Query a non-aggregated value
I want to do a simple query, but I can't figure out how to do it.  

I manage a sports league with 18 teams divided into three divisions (A,B,C).  At the end of each season, I append the 18 rows of team data onto the end of a master table.  So currently, there are three seasons of data in this chart totaling 54 rows. The teams move between the three divisions from season to season.  So some sample data would look like this.

Team       Season    Division

Team X    001          A

Team Y    001          B

Team X    002          A

Team Y    002          C 

Team X    003          B

Team Y    003          B

What I'm trying to do is create a query that shows me

Team     001    002    003

Team X   A        A       B

Team Y   B        C       B

I've attempted to create a query with a pivot, but I always get an error that says the pivot data has to be in the form of an aggregation.    I successfully created a similar pivot table where I was able to list out the Points by season exactly the same way I want the Divisions listed out, but of course, the Points were in the form of a sum(Points).  

Is this possible with a query, or is there some other function I should be using?

One reason why I'm using a query is that some of the teams also change from season to season, so the ultimate goal is to create a massive timeline chart showing every season across the top, with a each team getting its segment on the "timeline" showing the seasons they participated.  

---

Here's the sheet.

https://docs.google.com/spreadsheet/ccc?key=0Audv0CSq9hLOdHNQUGNQLUlYWEdpdDdhM0kzUHlqeWc#gid=0

a) Need help with the formula for pivoting out the items in the Season and Division row.  I want Seasons to extend across the top in the form of a Timeline, with the Division a Team was in under that season.  Division name is text, not an aggregatable number so it currently won't let me pivot out those Divisions.

b) sample formula is H3, but in actual usage, it will be in a different sheet referencing the data sheet.

c) My ultimate goal is to be able to take all the data from multiple seasons of results, and pivot or sum everything for one team into a single, comprehensive row.  For some data, such as Divisions, I'll want to pivot those out into individual seasons to show a timeline for that stat.  For some, such as Points, I'll want to have a sum of all the points for all the seasons.  The resulting row will likely contain around a hundred columns or more (and grow as seasons accumulate).  On top of that, I'll want some versions of this query to give me results sorted by certain stats. In the sample, it's sorted by Points.  I know I can probably do that sorting as a filtered or sorted table, but I'll want static pages that are dedicated to specific stats. 

d) Is it possible to pivot out something twice in the same query?  For example, if I'd like to have not just the Divisions pivoted out, but the Points as well.  So there would be two sets of columns of the seasons (001,002,003), but underneath each would be different sets of data.  

e) confused yet?  lol. 

f) thanks in advance! 

---------------------------------------------------------------------------------------------------------------------------

following is a solution using some helper cells