Re: Help with Lists in one column

On 3 Mar, 16:49, DA Morgan <damor..._at_psoug.org> wrote:
> 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
> damor..._at_x.washington.edu (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org- Hide quoted text -
>
> - Show quoted text -

Thanks Daniel but as I already said right now that is not an option. I need some sort of solution in the short term. Thanks anyway. Received on Mon Mar 03 2008 - 11:01:54 CST