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

# Rows to Coulmns

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
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
;``````