Help with Lists in one column

Hi,

I have a problem whereby I need to be able to obtain data from a column that stores multiple list values (comma separated) using one SQL Statement. An example is here:

DDL: CREATE TABLE TESTTBL

(ID		NUMBER		NOT NULL,
COMMENT01	VARCHAR2(4000)	NULL);

INSERT INTO TESTTBL(ID, COMMENT01)
VALUES(1,'Item1,Item2,Item3');

INSERT INTO TESTTBL(ID, COMMENT01)
VALUES(2,'Item1,Item2,Item3');

INSERT INTO TESTTBL(ID, COMMENT01)
VALUES(3,'Item1,Item2,Item3');

COMMIT; The results I need to obtain are:

ID COMMENT01

1	Item1
1	Item2
1	Item3
2	Item1
2	Item2
2	Item3
3	Item1
3	Item2
3	Item3

This needs to be linked in to a main query, for example:

SELECT ID, <OTHER COLUMNS>, COMMENT01
FROM TESTTBL... but I need to ensure that there is a separate row for each textual item in the COMMENT01 field. By the way, there are 10 of these COMMENT fields.

I appreciate this is bad DB design storing the list items this way but this is a legacy issue that we need a way of getting around so any ideas would be appreciated.

In addtion, I need this for Oracle 9, 10 (and 11), and SQL Server 2000 and 2005 and so a 'pure SQL' solution would be great. If not any other help is fine.

Thanks in advance! Received on Mon Mar 03 2008 - 09:36:10 CST