This discussion is archived
1 Reply Latest reply: Nov 14, 2012 3:02 PM by Frank Kulash RSS

Rows to Coulmns

Anita.I Newbie
Currently Being Moderated
hi guys,

I am having trouble converting rows to columns. Read a few articles on Pivot and Decode but still could not figure it out.
For eg.
Table info

Unit_no.                       Unit_id
001                                xyz
002                                ABC
003                                ABC
004                                XYZ
005                                XYZ
I would like the output to be
XYZ                                  ABC
001                                     -     
 -                                       002 
 -                                       003 
004                                      -
005                                      -
005
Thanks
  • 1. Re: Rows to Coulmns
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    Anita.I wrote:
    hi guys,

    I am having trouble converting rows to columns. Read a few articles on Pivot and Decode but still could not figure it out.
    For eg.
    Table info
    
    Unit_no.                       Unit_id
    001                                xyz
    002                                ABC
    003                                ABC
    004                                XYZ
    005                                XYZ
    I would like the output to be
    XYZ                                  ABC
    001                                     -     
    -                                       002 
    -                                       003 
    004                                      -
    005                                      -
    005
    I assume there's a mistake above, and you really only want one row of output that says 005.

    Here's one way to do that:
    SELECT       CASE (WHEN UPPER (unit_id) = 'XYZ' THEN unit_no END)     AS xyz
    ,       CASE (WHEN UPPER (unit_id) = 'ABC' THEN unit_no END)     AS abc
    FROM       info
    ORDER BY  unit_no
    ;
    Your problem is a little different from most pivot problems. In most of the examples you've seen, the result set probably had fewer rows than the original table. In your case, it seems like you want to keep the same number of rows, so you don't need the aggregate functions (such as MIN) or GROUP BY that (I assume) you saw in your reading.

    If you did have multiple rows in your table with the same unit_no, but you only wanted the results to have one row per unit_no, then you would have a more conventional-looking pivot:
    SELECT       MIN (CASE (WHEN UPPER (unit_id) = 'XYZ' THEN unit_no END))     AS xyz
    ,       MIN (CASE (WHEN UPPER (unit_id) = 'ABC' THEN unit_no END))     AS abc
    FROM       info
    GROUP BY  unit_no
    ORDER BY  unit_no
    ;
     

    I hope this answers your question.
    If not, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only), and also post the results you want from that data.
    Explain, using specific examples, how you get those results from that data.
    Always say which version of Oracle you're using (e.g., 11.2.0.2.0).
    See the forum FAQ {message:id=9360002}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points