Crosstab effect without summary values

How do I create a query which looks like this:

Month1   Month2   Month3
Red         Red         White
White      Blue        Blue

from a 2-field table which looks like this:

MonthName  Colour
Month1         Red
Month1         White
Month2         Red
Month2         Blue
Month3        White
Month3        Blue
LVL 5
BelloneAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
Bellone,

As mwvisa1 indicates, the t1 and t2 are simply table aliases.  Think of them as a "nickname" for the table, which allows the SQL statements to be more readable, and in some instances (as in this one) allows us to keep the logic straight.

To implement, wherever you see my table name "MonthColours", replace it with your actual table name.  Be sure to put in square brackets if your table name does not follow standard naming rules.

Patrick
0
 
Patrick MatthewsCommented:
1) Create a query like this, notionally named Step1:

SELECT t1.MonthName, t1.Colour, 
    (SELECT Count(t2.MonthName)
    FROM MonthColours t2
    WHERE t2.MonthName = t1.MonthName AND t2.Colour <= t1.Colour) AS RowNum
INTO CrosstabSource
FROM MonthColours t1
ORDER BY t1.MonthName, t1.Colour

Open in new window


That creates a new table.

2) Create a crosstab query to present the results:

TRANSFORM First(CrosstabSource.[Colour]) AS FirstOfColour
SELECT CrosstabSource.[RowNum]
FROM CrosstabSource
GROUP BY CrosstabSource.[RowNum]
PIVOT CrosstabSource.[MonthName];

Open in new window

0
 
BelloneAuthor Commented:
Hi MatthewsPatrick

Thanks very much for quick response.  Sorry to be dull but I'm not quite sure where t1 and t2 are coming from in the Step1 query, assuming my example table has been designated MonthColours

Bellone
0
 
Kevin CrossChief Technology OfficerCommented:
Since Patrick is showing you that you need a self join between MonthColours, you will need to remove ambiguity of where columns are coming from since you are guaranteed that every column has a duplicate name. Using aliases t1 and t2, SQL can understand the difference between each column in the query.
0
 
BelloneAuthor Commented:
OK, all clear now.  Many thanks to you both.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.