Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in04.usenetserver.com!news.usenetserver.com!nx01.iad01.newshosting.com!newshosting.com!post01.iad01!not-for-mail
Date: Mon, 03 Mar 2008 08:49:19 -0800
From: DA Morgan <damorgan@psoug.org>
Organization: Puget Sound Oracle Users Group
User-Agent: Thunderbird 2.0.0.12 (Windows/20080213)
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: Help with Lists in one column
References: <9e9c7cef-e470-4110-85f3-c571f84b661b@e6g2000prf.googlegroups.com>
In-Reply-To: <9e9c7cef-e470-4110-85f3-c571f84b661b@e6g2000prf.googlegroups.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Message-ID: <1204562959.414210@bubbleator.drizzle.com>
Cache-Post-Path: bubbleator.drizzle.com!unknown@dsl-216-162-218-178.drizzle.com
X-Cache: nntpcache 3.0.1 (see http://www.nntpcache.org/)
Lines: 68
X-Complaints-To: abuse@csolutions.net
Xref: usenetserver.com comp.databases.oracle.server:442059
X-Received-Date: Mon, 03 Mar 2008 11:49:19 EST (text.usenetserver.com)

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@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org