How to Unpivot, Crosstab, or Pivot using Oracle 9i with PL/SQL?
943723Jun 14 2012 — edited Jun 14 2012How to Unpivot, Crosstab, or Pivot using Oracle 9i with PL/SQL?
Here is a fictional sample layout of the data I have from My_Source_Query:
Customer | VIN | Year | Make | Odometer | ... followed by 350 more columns/fields
123 | 321XYZ | 2012 | Honda | 1900 |
123 | 432ABC | 2012 | Toyota | 2300 |
456 | 999PDQ | 2000 | Ford | 45586 |
876 | 888QWE | 2010 | Mercedes | 38332 |
... followed by up to 25 more rows of data from this query.
The exact number of records returned by My_Source_Query is unknown ahead of time, but should be less than 25 even under extreme situations.
Here is how I would like the data to be:
Column1 |Column2 |Column3 |Column4 |Column5 |
Customer | 123 | 123 | 456 | 876 |
VIN | 321XYZ | 432ABC | 999PDQ | 888QWE |
Year | 2012 | 2012 | 2000 | 2010 |
Make | Honda | Toyota | Ford | Mercedes|
Odometer | 1900 | 2300 | 45586 | 38332 |
... followed by 350 more rows with the names of the columns/fields from the My_Source_Query.
From reading and trying many, many, many of the posting on this topic I understand that the unknown number or rows in My_Source_Query can be a problem and have considered working with one row at a time until each row has been converted to a column.
If possible I'd like to find a way of doing this conversion from rows to columns using a query instead of scripts if that is possible. I am a novice at this so any help is welcome.