Re: horizontal group by?

"Michel Cadot" <micadot{at}altern{dot}org> wrote in message news:40e42b10$0$24429$636a15ce_at_news.free.fr...
>
> "Gordon" <gordon_t_wu_at_hotmail.com> a écrit dans le message de
> news:EBVEc.547$Ny6.1219_at_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
> >
> >
>
> If i correctly understand you, try something like:
>
> select yearA, count(*),
> sum(decode(fB,'x',1,0)) "fB=x",
> sum(decode(fB,'y',1,0)) "fB=y",
> from customer
> group by yearA
> /
>
> --
> Regards
> Michel Cadot
>
>

I'm sorry, group.
I've made a typo.
I really need to group the field hoizontally by fB<x (not fB=x). I didn't think its possible Received on Thu Jul 01 2004 - 13:38:29 CDT