split data in field to row using SQL2000

Question

Dear all, Please help me to find a solution. I have data in table like

ID    Code      

1     123,456,789,12  
2     456,073          
3     69,76,56

I need to list of code in row

ID    Code                 Ref

1     123,456,789,12       123

1     123,456,789,12       456

1     123,456,789,12       789

1     123,456,789,12       12

2     456,073              456

2     456,073              073

3     69,76,56             69

3     69,76,56             76

3     69,76,56             56

How do I do this in a query command? I'll be using the value in ref column to join another column in another tables. Thanks for supports

Answer 1

My first advice is to normalize your database. A column should contain a single piece of information. Your comma-delimited values violates this rule, which is why you're facing such difficulty. Since people seldom ever take that advice though, here's a kludge which might work for you. Since you're joining this to another table, you don't really need to separate out each value in its own column, you just need to be able to find a matching value in your column:

SELECT
    T1.id,
    T1.code,
    T2.ref
FROM
    My_Table T1
INNER JOIN Table_I_Am_Joining T2 ON
    T1.code LIKE '%,' + CAST(T2.ref AS VARCHAR(20)) + ',%' OR
    T1.code LIKE CAST(T2.ref AS VARCHAR(20)) + ',%' OR
    T1.code LIKE '%,' + CAST(T2.ref AS VARCHAR(20)) OR
    T1.code = CAST(T2.ref AS VARCHAR(20))

This relies on the codes in your column to be in an exact format, comma-delimited with no spaces. If that's not the case then this will likely not return what you're trying to get.

Answer 2

The answer is to normalize your database.

In the meantime, a workaround that will perform better on large sets, is to use a temp table. (LIKE searches can't use an index)

This approach also shows some steps towards normalizing the data and handles whitespace.

First create a "Tally Table" if you don't have one. This is a one-time deal, and Tally tables come in handy for all  kinds of things.

/*--- Create a Tally table.  This only needs to be done once.
    Note that "Master.dbo.SysColumns" is in all SQL 2000 installations.
    For SQL 2005, or later, use "master.sys.all_columns".
*/
SELECT TOP 11000   -- Adequate for most business purposes.
    IDENTITY (INT, 1, 1)    AS N
INTO
    dbo.Tally
FROM
    Master.dbo.SysColumns sc1,
    Master.dbo.SysColumns sc2

--- Add a Primary Key to maximize performance.
ALTER TABLE     dbo.Tally
ADD CONSTRAINT  PK_Tally_N PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

---

Now suppose your tables are:

CREATE TABLE ListO_Codes (ID INT IDENTITY(1,1), Code VARCHAR(88))

INSERT INTO ListO_Codes (Code)
SELECT      '123,456,789,12'        UNION ALL
SELECT      '456,073'               UNION ALL
SELECT      '69,76,56'


CREATE TABLE AnotherTable (ID INT IDENTITY(1,1), Ref VARCHAR(8), CodeWord VARCHAR (88))

INSERT INTO AnotherTable (Ref, CodeWord)
SELECT      '12',   'Children'      UNION ALL
SELECT      '123',  'of'            UNION ALL
SELECT      '456',  '-'             UNION ALL
SELECT      '789',  'sun,'          UNION ALL
SELECT      '073',  'see'           UNION ALL
SELECT      '56',   'your'          UNION ALL
SELECT      '69',   'time'          UNION ALL
SELECT      '76',   'has'

---

Then the temp table is:

CREATE TABLE    #NORMALIZED_Data (LOD_id INT, Ref int)  -- Make Ref varchar if it's not numeric
INSERT INTO
    #NORMALIZED_Data (LOD_id, Ref)
SELECT
    L.ID,
    -- Split Code string using Tally table and Delimiters
    LTrim (RTrim (SUBSTRING (',' + L.Code + ',', T.N+1, CHARINDEX (',', ',' + L.Code + ',', T.N+1) - T.N - 1 ) ) )
FROM
    dbo.Tally       T,
    ListO_Codes     L
WHERE
    T.N < LEN (',' + L.Code + ',')
AND
    SUBSTRING (',' + L.Code + ',', T.N, 1)   = ','


--- Index for performance
CREATE CLUSTERED INDEX CL_NORMALIZED_Data_LOD_id_Ref
ON #NORMALIZED_Data (LOD_id, Ref) WITH FILLFACTOR = 100

---

Then the search is:

SELECT
    L.ID,
    L.Code,
    A.Ref,
    A.CodeWord
FROM
    #NORMALIZED_Data    N
INNER JOIN
    ListO_Codes         L   ON N.LOD_id = L.ID
LEFT JOIN
    AnotherTable        A   ON N.Ref    = A.Ref
ORDER BY
    L.ID,
    A.Ref

---

And the results are:

ID    Code              Ref    CodeWord
--    --------------    ---    --------
1     123,456,789,12    12     Children
1     123,456,789,12    123    of
1     123,456,789,12    456    -
1     123,456,789,12    789    sun,
2     456,073           073    see
2     456,073           456    -
3     69,76,56          56     your
3     69,76,56          69     time
3     69,76,56          76     has