Split String In Columns By Delimiter Using Sql Server

I need to split my one column value in column using delimiter, below is the table structure. create table #a ( id int, timeline varchar(100) ) insert into #a values (1, '

Solution 1:

The SplitCSVToTable8K function being used in the following solution, is the same DelimitedSplit8K function mentioned above...

Here's how to use it in the solution:

WITH 
    cte_ParseTimeline AS (
        SELECT 
            a.id,
            rn =ROW_NUMBER() OVER (PARTITIONBY a.id ORDERBY sc.ItemNumber),
            sc.Item
        FROM 
            #a a
            CROSS APPLY dbo.SplitCSVToTable8K(REPLACE(REPLACE(a.timeline, '(', '/'), ')', '/'), '/') sc
        WHERE 
            sc.Item LIKE ('[A-Z][a-z][a-z]')
            OR 
            sc.Item LIKE ('[A-Z][a-z][a-z][a-z]')
        )
SELECT 
    pt.id,
    M1 =MAX(CASEWHEN pt.rn =1THEN pt.Item END),
    M2 =MAX(CASEWHEN pt.rn =2THEN pt.Item END),
    M3 =MAX(CASEWHEN pt.rn =3THEN pt.Item END),
    M4 =MAX(CASEWHEN pt.rn =4THEN pt.Item END),
    M5 =MAX(CASEWHEN pt.rn =5THEN pt.Item END),
    M6 =MAX(CASEWHEN pt.rn =6THEN pt.Item END)
FROM 
    cte_ParseTimeline pt
GROUPBY
    pt.id;

Results...

id          M1    M2    M3    M4    M5    M6
----------- ----- ----- ----- ----- ----- -----1           May   June  July  Nov   Dec   Jan
2           Feb   Mar   Apr   Aug   Sep   Oct
3           Aug   Sep   Oct   NULLNULLNULL4           May   June  July  NULLNULLNULL5           Nov   Dec   Jan   NULLNULLNULL6           Jan   Feb   Mar   July  Aug   Sep