Re: Help with Lists in one column

Paul wrote:
> 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!

Fix your design.

Putting multiple values into a single column is a violation of normalization rules and Database Basics 100 classroom curricula.

-- 
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org