Pivot, or crosstab in ANSI ?

[fuzzbox]

Hi

I have written a Crosstab query in MS access. I dont really think its
ANSI compliant, which I kinda need it to be.

For those of you who are uneducated in the ways of crosstab query, it
operates much like a pivot table in Excel.


If you have rows like this

A, March
B, June
A, Jan
D, June
A, March


And you run a crosstab you can get results like

Name,Jan, March, June
A 1 2 NULL
B NULL NULL 1
D NULL NULL 1


So I can sum the results per month and print the months out in a
tabular form, for nice reporting.


Now, to do this I do a

TRANSFORM Count(Month)
SELECT Name, Count(Month)
FROM Tbl
PIVOT Month


Which pivots it on Month.

My question is, if I need to use ANSII SQL to do this very same thing
... then how do I do it ?


Any help gratefully received.

Brian

 

[petersJazz]

No, this is not supported in standard SQL. What other RDBMS do you need it to work on?

 

[tlbroadbent]

You can write your own crosstab in SQL using the CASE function.

Select
[Name],
[Jan]=Sum(Case When Month = 'Jan' Then 1 Else 0 End),
[Mar]=Sum(Case When Month = 'Mar' Then 1 Else 0 End),
[Jun]=Sum(Case When Month = 'Jun' Then 1 Else 0 End)
From Tbl
Group By Name Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.