1 Reply Latest reply: Nov 14, 2012 5:02 PM by Frank Kulash RSS

    Rows to Coulmns

    Anita.I
      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
          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}