Path: text.usenetserver.com!out02a.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!y77g2000hsy.googlegroups.com!not-for-mail
From: Charles Hooper <hooperc2000@yahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Help with Lists in one column
Date: Mon, 3 Mar 2008 09:07:40 -0800 (PST)
Organization: http://groups.google.com
Lines: 225
Message-ID: <4a1cb43f-57b0-443c-9b0f-f50de8557d39@y77g2000hsy.googlegroups.com>
References: <9e9c7cef-e470-4110-85f3-c571f84b661b@e6g2000prf.googlegroups.com>
NNTP-Posting-Host: 205.208.133.102
Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1204564061 7480 127.0.0.1 (3 Mar 2008 17:07:41 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Mon, 3 Mar 2008 17:07:41 +0000 (UTC)
Complaints-To: groups-abuse@google.com
Injection-Info: y77g2000hsy.googlegroups.com; posting-host=205.208.133.102; 
 posting-account=xVXeFwkAAAAz3xgWc6VZyjXxx1jx4jb4
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 7.0; Windows NT 6.0; SLCC1; 
 .NET CLR 2.0.50727; Media Center PC 5.0; .NET CLR 3.0.04506),gzip(gfe),gzip(gfe)
Xref: usenetserver.com comp.databases.oracle.server:442065
X-Received-Date: Mon, 03 Mar 2008 12:07:41 EST (text.usenetserver.com)

On Mar 3, 10:36=A0am, Paul <paulwragg2...@hotmail.com> 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 =A0 =A0 =A0 =A0 =A0 =A0 NUMBER =A0 =A0 =A0 =A0 =A0NOT NULL,
> COMMENT01 =A0 =A0 =A0 VARCHAR2(4000) =A0NULL);
>
> 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 =A0 =A0 =A0COMMENT01
> ----------------------------------------------------------
> 1 =A0 =A0 =A0 Item1
> 1 =A0 =A0 =A0 Item2
> 1 =A0 =A0 =A0 Item3
> 2 =A0 =A0 =A0 Item1
> 2 =A0 =A0 =A0 Item2
> 2 =A0 =A0 =A0 Item3
> 3 =A0 =A0 =A0 Item1
> 3 =A0 =A0 =A0 Item2
> 3 =A0 =A0 =A0 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!

Thanks for posting the DDL and DML.

You will probably find that the there is no universal way to do this
that works on all database platforms.
One method that will work on versions of Oracle that support regular
expressions (note that this is a bit sloppy as it requires a DISTINCT
clause):
SELECT
  T.ID,
  SUBSTR(W.WORDS,1,20) WORDS
FROM
  TESTTBL T,
  (SELECT DISTINCT
    ID,
    REGEXP_SUBSTR(COMMENT01,'\w+',1,LEVEL) WORDS
  FROM
    TESTTBL W
  CONNECT BY
    REGEXP_SUBSTR(COMMENT01,'\w+',1,LEVEL) IS NOT NULL) W
WHERE
  T.ID=3DW.ID
ORDER BY
  T.ID,
  W.WORDS;

        ID WORDS
---------- -----
         1 Item1
         1 Item2
         1 Item3
         2 Item1
         2 Item2
         2 Item3
         3 Item1
         3 Item2
         3 Item3

The inline view uses REGEXP_SUBSTR and Oracle's CONNECT BY syntax to
break apart each word into a separate row.

Let's look at another way that will work with older versions of Oracle
(as well as Oracle 11g).  First, let's see if we can place each word
in a separate column.  In this example, I am only displaying the first
and third keywords:
SELECT
  ID,
  SUBSTR(DECODE(SPACE_1,0,NULL,SUBSTR(NEW_KEYWORD,1,SPACE_1-1)),1,20)
SUB_KEYWORD1,
  SUBSTR(DECODE(SPACE_3,0,NULL,SUBSTR(NEW_KEYWORD,SPACE_2+1,SPACE_3-
SPACE_2-1)),1,20) SUB_KEYWORD3
FROM
  (SELECT
    ID,
    TRIM(COMMENT01)||',' NEW_KEYWORD,
    INSTR(TRIM(COMMENT01)||',',',',1,1) SPACE_1,
    INSTR(TRIM(COMMENT01)||',',',',1,2) SPACE_2,
    INSTR(TRIM(COMMENT01)||',',',',1,3) SPACE_3,
    INSTR(TRIM(COMMENT01)||',',',',1,4) SPACE_4,
    INSTR(TRIM(COMMENT01)||',',',',1,5) SPACE_5,
    INSTR(TRIM(COMMENT01)||',',',',1,6) SPACE_6,
    INSTR(TRIM(COMMENT01)||',',',',1,7) SPACE_7,
    INSTR(TRIM(COMMENT01)||',',',',1,8) SPACE_8,
    INSTR(TRIM(COMMENT01)||',',',',1,9) SPACE_9,
    INSTR(TRIM(COMMENT01)||',',',',1,10) SPACE_10,
    INSTR(TRIM(COMMENT01)||',',',',1,11) SPACE_11,
    INSTR(TRIM(COMMENT01)||',',',',1,12) SPACE_12,
    INSTR(TRIM(COMMENT01)||',',',',1,13) SPACE_13
  FROM
    TESTTBL);

ID SUB_KEYWORD1         SUB_KEYWORD3
-- -------------------- ------------
 1 Item1                Item3
 2 Item1                Item3
 3 Item1                Item3

But we want each word on a separate row, so we need some sort of a
counter to cycle through the words to accomplish this:
SELECT
  ROWNUM RN
FROM
  DUAL
CONNECT BY
  LEVEL<=3D13;

        RN
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
        11
        12
        13

Now, let's create a Cartesian join between the table and the counter,
manually expanding the formulas for SPACE_1, SPACE_2, SPACE_3 as
needed from the previous test SQL statement:
SELECT
  ID,
 SUBSTR(DECODE(
  INSTR(TRIM(COMMENT01)||',',',',1,RN) ,0,NULL,
  SUBSTR(COMMENT01||',',
    DECODE(RN,1,0,INSTR(TRIM(COMMENT01)||',',',',1,RN-1))+1,
    INSTR(TRIM(COMMENT01)||',',',',1,RN)
     - DECODE(RN,1,0,INSTR(TRIM(COMMENT01)||',',',',1,RN-1)) -1))
  ,1,20) WORDS
FROM
  TESTTBL,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=3D13);

The problem that we have now is that NULL words are being included, so
we slide the above into an inline view to filter out the NULLs:
SELECT
  ID,
  WORDS
FROM
(SELECT
  ID,
 SUBSTR(DECODE(
  INSTR(TRIM(COMMENT01)||',',',',1,RN) ,0,NULL,
  SUBSTR(COMMENT01||',',
    DECODE(RN,1,0,INSTR(TRIM(COMMENT01)||',',',',1,RN-1))+1,
    INSTR(TRIM(COMMENT01)||',',',',1,RN)
     - DECODE(RN,1,0,INSTR(TRIM(COMMENT01)||',',',',1,RN-1)) -1))
  ,1,20) WORDS
FROM
  TESTTBL,
  (SELECT
    ROWNUM RN
  FROM
    DUAL
  CONNECT BY
    LEVEL<=3D13))
WHERE
  WORDS IS NOT NULL
ORDER BY
  ID,
  WORDS;

        ID WORDS
---------- -----
         1 Item1
         1 Item2
         1 Item3
         2 Item1
         2 Item2
         2 Item3
         3 Item1
         3 Item2
         3 Item3

I am not sure how you might implement this on SQL Server.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.