Showing the value of 5 columns as 5 rows
19284Oct 24 2003 — edited Oct 29 2003Hi,
I'm working on an 9.2 database and I have a table which contains 5 columns of the same datatype. Example of the definition of the table:
ID NUMBER(10)
COL1 VARCHAR2(128)
COL2 VARCHAR2(128)
COL3 VARCHAR2(128)
COL4 VARCHAR2(128)
COL5 VARCHAR2(128)
For some reason I have to write a SELECT-statement which must return 5 rows for each ID where each row contains a value of the COLx-columns. Example: if my table contains the following information:
ID COL1 COL2 COL3 COL4 COL5
1 AAA BBB CCC DDD EEE
2 FFF GGG HHH III JJJ
then my select statement must return:
1 AAA
1 BBB
1 CCC
1 DDD
1 EEE
2 FFF
2 GGG
2 HHH
2 III
2 JJJ
I know I can use UNION ALL together with 5 SELECT-statements, but is there another (and faster?) way?
Regards,
Vincent Botteman