Re: Help with Lists in one column

On Mar 3, 10:36 am, Paul <paulwragg2..._at_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             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!

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=W.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

-- -------------------- ------------
 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<=13;

        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<=13);

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<=13)) WHERE WORDS IS NOT NULL ORDER BY 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. Received on Mon Mar 03 2008 - 11:07:40 CST