Re: query to output result horizontally

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Sat, 14 Mar 2009 08:09:26 +0100
Message-ID: <49bb5831$0$199$e4fe514c_at_news.xs4all.nl>

yababy_at_gmail.com schreef:
> On Mar 13, 9:34 am, songyu <songy..._at_gmail.com> wrote:

>> I could not figure out a query to display the output below. I'm
>> running 10g.
>>
>> Table record
>> ============
>> GRP     NAME    XTRANS  QUANTITY
>> 1       AAA         CX        10
>> 1       AAA         YP        20
>> 1       BBB         CX       100
>> 1       BBB         YP       200
>> 2       AAA         CX        80
>> 2       AAA         YP        81
>> 2       BBB         CX        90
>> 2       BBB         YP        91
>> 3       AAA         CX       123
>> 3       AAA         YP       124
>> 3       BBB         CX      1000
>> 3       BBB         YP      2000
>>
>> OUTPUT
>> ======
>>            AAA             BBB
>>         CX      YP      CX      YP
>> 1       10      20      100     200
>> 2       80      81       90      91
>> 3      123     124     1000    2000
>>
>> I can have multiple groups available say 1-10 but only two disinct
>> names (AAA, BBB) and two distinct XTRANS (CX,YP).
>>
>> Testcase
>> ========
>> create table testtable (grp number, name varchar2(3), xtrans varchar2
>> (2), quantity number);
>> insert into testtable values (1,'AAA','CX',10);
>> insert into testtable values (1,'AAA','YP',20);
>> insert into testtable values (1,'BBB','CX',100);
>> insert into testtable values (1,'BBB','YP',200);
>> insert into testtable values (2,'AAA','CX',80);
>> insert into testtable values (2,'AAA','YP',81);
>> insert into testtable values (2,'BBB','CX',90);
>> insert into testtable values (2,'BBB','YP',91);
>> insert into testtable values (3,'AAA','CX',123);
>> insert into testtable values (3,'AAA','YP',124);
>> insert into testtable values (3,'BBB','CX',1000);
>> insert into testtable values (3,'BBB','YP',2000);
>> commit;
>>
>> thanks in advance.

>
> This should work for you
>
> SQL> CREATE TABLE tmp (grp NUMBER(22,0), NAME VARCHAR2(10), xtrans
> VARCHAR2(10), quantity NUMBER(22,0))
> Table created.
> SQL> insert into TMP VALUES(1,'AAA','CX',10)
> 1 row created.
> SQL> insert into TMP VALUES(1,'AAA','YP',20)
> 1 row created.
> SQL> insert into TMP VALUES(1,'BBB','CX',100)
> 1 row created.
> SQL> insert into TMP VALUES(1,'BBB','YP',200)
> 1 row created.
> SQL> insert into TMP VALUES(2,'AAA','CX',80)
> 1 row created.
> SQL> insert into TMP VALUES(2,'AAA','YP',81)
> 1 row created.
> SQL> insert into TMP VALUES(2,'BBB','CX',90)
> 1 row created.
> SQL> insert into TMP VALUES(2,'BBB','YP',91)
> 1 row created.
> SQL> insert into TMP VALUES(3,'AAA','CX',123)
> 1 row created.
> SQL> insert into TMP VALUES(3,'AAA','YP',124)
> 1 row created.
> SQL> insert into TMP VALUES(3,'BBB','CX',1000)
> 1 row created.
> SQL> insert into TMP VALUES(3,'BBB','YP',2000)
> 1 row created.
> SQL> commit
> Commit complete.
> SQL> SELECT grp,
> NAME,
> xtrans,
> quantity
> FROM tmp
>
> GRP NAME XTRANS QUANTITY
> ---------- ---------- ---------- ----------
> 1 AAA CX 10
> 1 AAA YP 20
> 1 BBB CX 100
> 1 BBB YP 200
> 2 AAA CX 80
> 2 AAA YP 81
> 2 BBB CX 90
> 2 BBB YP 91
> 3 AAA CX 123
> 3 AAA YP 124
> 3 BBB CX 1000
> 3 BBB YP 2000
>
>
> 12 rows selected.
> SQL> SELECT grp,
> SUM ( CASE NAME || xtrans
> WHEN 'AAACX' THEN quantity
> END ) aaa_cx,
> SUM ( CASE NAME || xtrans
> WHEN 'AAAYP' THEN quantity
> END ) aaa_yp,
> SUM ( CASE NAME || xtrans
> WHEN 'BBBCX' THEN quantity
> END ) bbb_cx,
> SUM ( CASE NAME || xtrans
> WHEN 'BBBYP' THEN quantity
> END ) bbb_yp
> FROM tmp
> GROUP BY GRP
>
> GRP AAA_CX AAA_YP BBB_CX BBB_YP
> ---------- ---------- ---------- ---------- ----------
> 1 10 20 100 200
> 2 80 81 90 91
> 3 123 124 1000 2000
>
>
> 3 rows selected.
>
> good luck.

And what if another NAME is added to the table? Don't use hard coded values in your queries! Look for 'pivot' in this group...

Shakespeare Received on Sat Mar 14 2009 - 02:09:26 CDT