Re: can we output the select results "horizontal" in sqlplus?

From: CusnIt <gwstairs_at_hotmail.com>
Date: Sun, 22 Dec 2002 21:41:53 GMT
Message-ID: <BkqN9.4825$A17.333247_at_ursa-nb00s0.nbnet.nb.ca>

A friend of mine claims that "ANYTHING" can be done using SQL. Not sure why you would want to but here is a solution

SELECT

  R1.ROW1VAL,
  R2.ROW2VAL,
  R3.ROW3VAL

FROM
  (SELECT MIN(ROW1.VALUE) ROW1VAL,

           ROW1.DEPARTMENT DEPT
     FROM  MYTEST01 ROW1

 GROUP BY DEPARTMENT) R1,
  (SELECT MIN(ROW2.VALUE) ROW2VAL,

           ROW2.DEPARTMENT DEPT
     FROM  MYTEST01 ROW2
    WHERE  ROW2.VALUE > (SELECT MIN(VALUE)
                        FROM MYTEST01
        WHERE DEPARTMENT = ROW2.DEPARTMENT)
                       GROUP BY DEPARTMENT) R2,
  (SELECT  MAX(ROW3.VALUE) ROW3VAL ,
           ROW3.DEPARTMENT DEPT
     FROM  MYTEST01 ROW3

 GROUP BY DEPARTMENT) R3
WHERE R1.DEPT = R2.DEPT
AND R2.DEPT = R3.DEPT "Kaidi" <kzhao_at_cs.uic.edu> wrote in message news:6f054532.0212172131.82f7026_at_posting.google.com...
> Hi!
> Anyone has any idea of this?
> I need to output some select results "horizontal" rather than
> "vertically" in sqlplus, what I mean is:
>
> given the table:
> mytable:
>
> department value
> dept1 1
> dept1 2
> dept1 3
> dept2 4
> dept2 5
> dept2 6
> ..... ...
>
> I want to output something like:
> select xxxxxxxxxx;
>
> dept1 1 2 3
> dept2 4 5 6
> ..... ... ... ...
>
> Anyone has any idea of how to do this in sqlplus?
> Thanks a lot for your kind help.
> ----
> PS: for your convenience, I put the sql to create the test table as
> following:
>
> create table mytest01 (department varchar2(10), value number);
> insert into mytest01 values ('dept1', 1);
> insert into mytest01 values ('dept1', 2);
> insert into mytest01 values ('dept1', 3);
> insert into mytest01 values ('dept2', 4);
> insert into mytest01 values ('dept2', 5);
> insert into mytest01 values ('dept2', 6);
> insert into mytest01 values ('dept3', 7);
> insert into mytest01 values ('dept3', 8);
> insert into mytest01 values ('dept3', 9);
>
> the result I would like is:
> select xxxxxxxxxxx......;
>
> dept1 1 2 3
> dept2 4 5 6
> dept3 7 8 9
> .......
Received on Sun Dec 22 2002 - 22:41:53 CET