horizontal group by?

From: Gordon <gordon_t_wu_at_hotmail.com>
Date: Thu, 1 Jul 2004 10:56:33 -0400
Message-ID: <EBVEc.547$Ny6.1219@mencken.net.nih.gov>

Hi Group,

I'm wondering if its possible to generate the following report with a single SQL statement:

The original report is easy, it looks like:

Year A| new_cust
------|------------
1990  | 3000
1991  | 4000
1992  | 5000

All I needed to do here is:
SELECT count(cust_id), yearA
FROM customer
Group by yearA

Now the requirement gets interesting.., they want me to to split the new_cust horizontally based on another field (say, fB). So the report looks like:

Year A  | new_cust    | new_cust when fB=x | new_cust when fB=y
--------|----------------------------------|---------------------
1990    | 3000        | 1000               | 2000
1991    | 4000        | 2000               | 2000
1992    | 5000        | 4000               | 1000

Its almost seems like I got to somehow perform another group-by horizontally.
How should I generate this report in a single? Is it possible?

Thanks Received on Thu Jul 01 2004 - 09:56:33 CDT