Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: SQL Crosstab

Re: SQL Crosstab

From: Greg Hayes <greg_at_hayford.demon.co.uk>
Date: 1997/05/14
Message-ID: <S5iOrCAvEZezEwUb@hayford.demon.co.uk>#1/1

In article <337c09ed.3215002_at_newshost>, Thomas Kyte <tkyte_at_us.oracle.com> writes
>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)
>>
>
>I'll assume you mean I have a table with A, B, and C.
>A takes the values AA, BB, CC, DD, EE
>B takes the values 01, ... 05
>C is some number.
>
>Take B from being in record to cross record..
>
>>
>> 01 02 03 04 05
>>
>>AA 357 23 5 67 14
>>BB 34 135 77 267 17
>>CC 111 346 13 43 346
>>DD 3 7 23 198 11
>>EE 17 4 13 12 4
>>
>>
>>with and without the use of DECODE. Does anyone have a solution for 2
>>variables when the range of values that the variables can take is
>>unknown (say char(20)).
>>
>
>With decode, its easy:
>
>select a,
> sum( decode( b, '01', c, NULL ) ) '01',
> sum( decode( b, '02', c, NULL ) ) '02',
> ...
> sum( decode( b, '05', c, NULL ) ) '05'
>from T
>group by a
>/
>
>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
>/
>
>decode will be faster, easier...
>
>
>Using 2 variables is just as easy as one. Take the above example and pretent
>that T was really:
>
>create view T ( a, b, c )
>as
>select A, b1 || b2, c
>from AnotherT
>/
>
>Just concatenate the values together in the decode, replace B with B1||B2.
>
>
>>--
>>Greg Hayes
>>greg_at_hayford.demon.co.uk
>
>
>Thomas Kyte
>tkyte_at_us.oracle.com
>Oracle Government
>Bethesda MD
>
>http://govt.us.oracle.com/ -- downloadable utilities
>
>----------------------------------------------------------------------------
>Opinions are mine and do not necessarily reflect those of Oracle Corporation
No there is no variable c. The crosstab is counting the occurences of the combinations of the values of the variables a and b. This is a common problem which is very easy to program in other query languages.

-- 
Greg Hayes
Received on Wed May 14 1997 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US