SQL : Split Delimited Column into Multiple Columns Without External Function

Without making any external code or CLR, when we try to split a column into multiple column based on the delimeter present in that column, it can be achieved with XML methods present(SQL server 2005 or above).

When number of dseired columns are less(say 2 or 3), it is quite easy with the help of character functions say CHARINDEX, PATINDEX, STUFF, SUBSTRING, REPLACE etc and if the number of desired columns are less than 5, then PARSENAME() can be used. For that you need to replace the delimeter to dot(.) and then simpy use PARSENAME() which is actually for the purpsose to parse a sql statement into segments say server, databse, schema, datatable etc.

E.g.  select   PARSENAME('1.2.3.4',2)-- = 3

**  , PARSENAME('1.2.3.4',4)-- = 1**

Or you can use some external functions to pass every row data into that function with deliemter type, loop through/ cursor to get the indexes of delimeters, get the values, replacing the actual string and keep on going till the end. 

Well, there are lots of example for the XML methods that I am attaching here(I am not saying this is comletely my discovery), but when you try hard and get soemthing eary technique it is always human nature to let others know, same thing with me :). 

External requirement : you have to add an unique id(not necessarily identity key) to each of the rows containing the rows containing delimeter. In the example ID field is added. It is needed to have one ID unique field.

Step 1 : make a temporary storage of data separated by the delimeter in a single column with the help of the CTE. Here using XML functions, the whole rows are converted into a single column and there we are maintaing the column number also as separate field.

Step 2 : Using Pivot method, you cna then convert the rows into columns based on the column number mentioned above. In order to use PIVOT on the column number( max(on the id field) ), you need to keep the name of the fields as integer 1,2,3,4 etc etc while PIVOTING.

DECLARE @MainTable TABLE ( ID INT, DelimetedCol VARCHAR(1000) ) 
INSERT INTO @MainTable VALUES (1, '01, 02, 03, 04, 05, 06, 07, 08, 09, 10') 
INSERT INTO @MainTable VALUES (2, '11, 12, 13, 14, 15, 16, 17, 18, 19, 20') 
INSERT INTO @MainTable VALUES (3, '21, 22, 23, 24, 25, 26, 27, 28, 29, 30') 
INSERT INTO @MainTable VALUES (4, '31, 32, 33, 34, 35, 36, 37, 38, 39, 40') 
INSERT INTO @MainTable VALUES (5, '41, 42, 43, 44, 45, 46, 47, 48, 49, 50') 
INSERT INTO @MainTable VALUES (6, '51, 52, 53, 54, 55, 56, 57, 58, 59, 60') 
 
; WITH CTE AS  
( 
        SELECT    A.ID, 
                B.IND_ROW, 
                ROW_NUMBER() OVER ( PARTITION BY A.ID ORDER BY A.ID) AS ROW_NUM 
        FROM 
        ( 
                SELECT    *, 
                        CONVERT( XML, '<ROW>' + REPLACE( C.DelimetedCol, ',', '</ROW><ROW>' ) + '</ROW>' ) AS XML_ROW 
                FROM    @MainTable C 
        ) A 
        CROSS APPLY 
        ( 
                SELECT    DATA.ROW.value('.', 'VARCHAR(100)') AS IND_ROW 
                FROM    A.XML_ROW.nodes('ROW') AS DATA(ROW) 
        ) B 
) 
 
SELECT    ID, [1] AS COL_1, [2]  AS COL_2, [3]  AS COL_3, [4]  AS COL_4, [5]  AS COL_5, [6]  AS COL_6, [7]  AS COL_7, [8]  AS COL_8, [9]  AS COL_8, [10] AS COL_10 
FROM  
( 
        SELECT    ID 
                , ROW_NUM 
                , IND_ROW  
        FROM    CTE 
)        AS SOURCE_TABLE 
PIVOT 
(     
        MAX(IND_ROW) FOR ROW_NUM IN ( [1], [2], [3], [4], [5], [6], [7], [8], [9], [10] ) 
)        AS PIVOT_TABLE

regards
joon