[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Crosstab effect without summary values

Posted on 2011-09-16
5
Medium Priority
?
410 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
[X]
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
  • 2
  • 2
5 Comments
 
LVL 93

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 60

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 93

Accepted Solution

by:
Patrick Matthews earned 2000 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

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

650 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