Results 1 to 8 of 8
  1. #1
    Join Date
    Jan 2004
    Posts
    3

    Unanswered: Query to fetch rows as columns

    Hi,

    I need to write the query which selects rows as columns.
    Suppose a table Friends is having name of 10 friends, and
    Select name from friends; Returns:
    Piyush
    Mohit
    Himanshu
    Murali
    .
    .
    .
    .

    I like thise to be returned as columns;
    Piyush Mohit Himanshu Murali . . . .

    Thanks,

  2. #2
    Join Date
    Nov 2003
    Posts
    87
    You just wanna display output like that?

    If so

    SQL> declare
    2 cName varchar2(4000);
    3 n tname.name%type;
    4 cursor c1 is
    5 select name
    6 from tname;
    7 begin
    8 open c1;
    9 loop
    10 fetch c1 into n;
    11 exit when c1%notfound;
    12 cName := cName || ' ' || n;
    13 end loop;
    14 dbms_output.put_line(cName);
    15 close c1;
    16 end;
    17 /
    divya anoop jithesh remya soorya amit

    PL/SQL procedure successfully completed.

  3. #3
    Join Date
    Jan 2004
    Posts
    3
    Thanks raseena,
    I want the same work done with single query, no pl/sql block.
    Is there any way to do that?

  4. #4
    Join Date
    Dec 2003
    Posts
    13
    Originally posted by Piyushmi
    Thanks raseena,
    I want the same work done with single query, no pl/sql block.
    Is there any way to do that?

    Yes you can do it by using other programming language
    If you want use only SQL then try with CASE STATEMENT

    regards
    nanaiah

  5. #5
    Join Date
    Jan 2004
    Location
    India
    Posts
    62

    Angry

    You can do it by a single query with the help of DECODE function.
    Sorry to say but right now I don't have the proper syntax for this.
    If you have a book of Oracle press for PL/Sql then check it.
    The syntax to display rows as columns is given in it.
    Sorry, I cann't give you the proper syntax right noe.
    But I have read this thing in Oracle press's book for pl/sql.
    (Actually that time I never thought that someone will want this facility!)
    Any ways.
    Good luck.
    Do try DECODE.
    Regards,

    Rushi

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    If altering your table suits your needs, this could help:
    Code:
    ALTER TABLE friends ADD id number(2);
    Update ID's in order to form a sequence. Then, your query should return
    Code:
    SELECT * FROM friends;
    
    ID NAME
    --  --------
    1   Piyush
    2   Mohit
    3   Himanshu
    4   Murali
    ...
    10 Rajiv
    You MUST have definite number of rows and known values in your FRIENDS table in order for this query to work:
    Code:
    SELECT 
      MAX(DECODE(id, 1, name, NULL)) n1,
      MAX(DECODE(id, 2, name, NULL)) n2,
      MAX(DECODE(id, 3, name, NULL)) n3,
      ...
      MAX(DECODE(id, 10, name, NULL)) n10
    FROM friends;
    
    n1        n2      n3            n4 ...          n10
    -------  ------- -----------  -------      ------
    Piyush  Mohit  Himanshu  Murali .... Rajiv
    Last edited by Littlefoot; 01-19-04 at 09:36.

  7. #7
    Join Date
    Jan 2004
    Location
    India
    Posts
    62
    code:--------------------------------------------------------------------------------
    SELECT
    MAX(DECODE(id, 1, name, NULL)) n1,
    MAX(DECODE(id, 2, name, NULL)) n2,
    MAX(DECODE(id, 3, name, NULL)) n3,
    ...
    MAX(DECODE(id, 10, name, NULL)) n10
    FROM friends;

    n1 n2 n3 n4 ... n10
    ------- ------- ----------- ------- ------
    Piyush Mohit Himanshu Murali .... Rajiv
    --------------------------------------------------------------------------------

    instead of adding a column, you can use rownum in the above query.
    What I was trying to tell you was exactly waht Littlefoot said.

    SELECT
    MAX(DECODE(rownum, 1, name, NULL)) n1,
    MAX(DECODE(rownum, 2, name, NULL)) n2,
    MAX(DECODE(rownum, 3, name, NULL)) n3,
    ...
    MAX(DECODE(rownum, 10, name, NULL)) n10
    FROM friends;

    n1 n2 n3 n4 ... n10
    ------- ------- ----------- ------- ------
    Piyush Mohit Himanshu Murali .... Rajiv
    --------------------------------------------------------------------------------
    Regards,

    Rushi

  8. #8
    Join Date
    Jan 2004
    Posts
    3
    Thanks Everybody,

    this max(decode....... logic is really good, and suits my requirement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •