Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Crosstab effect without summary values

Posted on 2011-09-16
5
383 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Bellone
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36548816
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
 
LVL 5

Author Comment

by:Bellone
ID: 36549055
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
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36549245
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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 36549290
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
 
LVL 5

Author Closing Comment

by:Bellone
ID: 36549441
OK, all clear now.  Many thanks to you both.
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Creating and Managing Databases with phpMyAdmin in cPanel.
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

838 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question