FYI - Sql for splitting a delimited concatenated string into separate strings/rows.

[Dr. StrangeLove]

Greetings,

Let say we want to split column 'list' in table lists
into separate rows using the comma as the delimiter.
Table lists
id list
1 aa,bbb,c
2 e,f,gggg,hh
3 ii,kk
4 m
5 nn,pp
6 q,RRRRRRR,s

First we need a table of consecutive integers from 1 to say 100.
Table numbers has a single column 'digit'.The largest digit
should be >= the length of the largest string to split (list).
digit
1
2
3
..
100

Now we can use this query:

SELECT [id], Mid(',' &

• & ',',[digit]+1,Instr([digit]+1,',' &
  • &
    ',',',')-([digit]+1)) AS [string]
    FROM lists, numbers
    WHERE digit=Instr(digit,',' & list & ',',',') And digit<len(',' & list)
    ORDER BY [id],[digit];
    
    id string
    1 aa
    1 bbb
    1 c
    2 e
    2 f
    2 gggg
    2 hh
    3 ii
    3 kk
    4 m
    5 nn
    5 pp
    6 q
    6 RRRRRRR
    6 s
    
    Modifying the query to handle any type of delimiter
    of any length is left as an exercise
    
    You don't need to write functions for many operations
    (such as forming concatenated strings from rows) that you have
    been told you need!.
    
    For crosstabs and much more in Sql Server check out RAC.
    Free query tool for any Sql Server version - QALite.
    Check out www.rac4sql.net