Re: query to output result horizontally

From: <yababy_at_gmail.com>
Date: Fri, 13 Mar 2009 13:04:33 -0700 (PDT)
Message-ID: <86570663-0a24-4333-a2db-d218d930ae27_at_a5g2000pre.googlegroups.com>

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. Received on Fri Mar 13 2009 - 15:04:33 CDT