Re: Help with Lists in one column
Date: Mon, 3 Mar 2008 09:17:28 -0800 (PST)
Message-ID: <f44e5601-5d97-45ae-acdc-fe5dc5371a11@n75g2000hsh.googlegroups.com>
On 3 Mar, 17:07, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> 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
> 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<=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;
>
> 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.- Hide quoted text -
>
> - Show quoted text -
Wow - I knew posting the DDL & DML was a good idea :-)
I am just working on the SQL Server version at the moment as I think I have a solution to that now thanks to a helpful hint from another poster but I shall take a good look at this tomorrow and make sure I understand exactly what is happening!
This is some very impressive SQL - thanks very much Charles. Received on Mon Mar 03 2008 - 11:17:28 CST