Convert multiple columns to single column & multiple rows
570672Nov 16 2007 — edited Nov 16 2007Hi,
I am working on converting multiple columns in a table to a single column and multiple rows. The number of columns in the table can vary per record. Not all columns have a value for each record. The current table I am working on has a record with 85 columns having a value..but I anticipate that this may get bigger in the future and could exten upto 200 columns for some records.
The same table I have is of the format:
Table A
Refnum Col1 Col2 Col3 Col4 Col5 .......Col200
A1231 122 1547 5156 515 566 ........789
B7989 989 153 551 11 5 ........114
G6868 1 125 1545
J678 12
K8799 7879 156 4545 1145
P908 112 168 158
I would like to convert Table A to the following format:
Refnum Col1
A1231 122
1547
5156
515
566
.
.
789
B7989 989
153
551
11
5
.
.
114
G6868 1
125
1545
J678 12
K8799 7879
156
4545
1145
P908 112
168
158
It is similar to a table transpose, but not really a transpose as all the columns need to converted into a single column.
If anyone is aware of how to achieve this in Oracle, please advise and guide me.
Thanks,
Steve