Converting Column to Rows
824061Dec 15 2010 — edited Dec 16 2010Hello,
I am trying to build and SQL to convert columns from multiple rows to the all rows - see below test data and result expected:
CREATE TABLE XX_TEST(NAME VARCHAR2(10),A1 VARCHAR2(10),A2 VARCHAR2(10), A3 VARCHAR2(10),A4 VARCHAR2(10),A5 VARCHAR2(10));
INSERT INTO XX_TEST VALUES('LIST','A','B','C','D','E');
INSERT INTO XX_TEST VALUES('L1','1',NULL,'3',NULL,NULL);
INSERT INTO XX_TEST VALUES('L2','1','5','4',NULL,NULL);
COMMIT
SELECT * FROM XX_TEST;
Result expected:
NAME is Column from table XX_TEST but COLUMN and VALUE are the columns converted to rows-
NAME COLUMN VALUE
L1 A1 1
L1 A2 NULL
L1 A3 3
L1 A4 NULL
L1 A5 NULL
L2 A1 1
L2 A2 5
L2 A3 4
L2 A4 NULL
L2 A5 NULL
Thanks
BS