Re: SQL Crosstab

Thomas Kyte wrote:
>
> On Tue, 13 May 1997 15:47:11 +0100, Greg Hayes <greg_at_hayford.demon.co.uk> wrote:
>
> >
> >Given a table:
> >
> >a char(2)
> >b char(2)
> >
> >a takes the values AA, BB, CC, DD, EE
> >b takes the values 01, 02, 03, 04, 05
> >
> >
> >how easy is it (or even possible to produce a cross tab of a by b)
>
> Without decode, it would require you to join the table to itself N times, for
> example
>
> select t1.a, t1.c, t2.c, t3.c, t4.c, t5.c
> from t t1, t t2, t t3, t t4, t t5
> where t1.b = '01'
> and t2.b = '02'
> and t3.b = '03'
> and t4.b = '04'
> and t5.b = '05'
> and t1.a = t2.a = t3.a = t4.a = t5.a

IF you know the values of b in advance, and those values aren't substrings on one-another, you can use the following trick (limited excerpt shown):

select a, sum(instr(b, '01') * c), sum(instr(b, '02') * c), ... from table
group by a;

-kdg Received on Wed May 14 1997 - 00:00:00 CDT