Segregate concatenated values into new separate columns using SQL Server

Question

I have one column that have more than one value concatenated that entered as free text. Now, I want to segregate each value in a new column and I can guess the max number of columns based one the maximum number of concatenated values.

How to achieve this? Thanks a lot in advance.

Use charindex to find the place & substring to get the correct text — James Z, Oct 25 '15 at 06:16
Possible duplicate of [How do I split a string so I can access item x](http://stackoverflow.com/questions/2647/how-do-i-split-a-string-so-i-can-access-item-x) — Backs, Oct 25 '15 at 07:16

score 0 · Answer 1 · answered Oct 25 '15 at 10:33

with CTE  as (
select 'Jhon,Keen,Susan' as Concatenated
union all select 'Jims,Huda' as Concatenated
union all select 'Boul,Rees' as Concatenated
union all select 'Lora' as Concatenated

)
,CTE_xml as (
    select Concatenated,CONVERT(xml,' <root> <s>' + REPLACE(Concatenated,',','</s> <s>') + '</s>   </root> ') as Concatenated_xml
     from CTE
)
,CTE_Unpivot as (
    select CTE_xml.Concatenated,
            T.c.value('.','varchar(max)') as Val,
            'Col'+(T.c.value('for $i in . return count(../*[. << $i]) + 1', 'varchar(max)')) as Name
         from CTE_xml
cross APPLY Concatenated_xml.nodes('/root/s')  T(c)
)

select * from 
    (SELECT CTE_Unpivot.Concatenated, CTE_Unpivot.Val,CTE_Unpivot.Name  from CTE_Unpivot) as Src
    pivot (
        max(src.Val)
        for Name in ([Col1], [Col2], [Col3], [Col4],[Col5],[Col6],[Col7],[Col8])-- in ([Col1], [Col2], [Col3], [Col4])
    )as pvt

This one is hard coded to upto 8 columns, it case it is determined, you can add more. If you want it to be fully dynamic, you have to make the Pivot query to be dynamic.

Thanks a lot OSAMA for your prompt reply. I need it to be dynamic, so how can I do it? — Harreni, Oct 26 '15 at 11:38

score 0 · Answer 2 · answered Oct 27 '15 at 10:08

To Use Dynamic Query.

declare @columnVar varchar(500);
declare @Query varchar(5000);
/*This first query to get the columns name*/
with CTE  as (
select 'Jhon,Keen,Susan' as Concatenated
union all select 'Jims,Huda' as Concatenated
union all select 'Boul,Rees' as Concatenated
union all select 'Lora' as Concatenated

)
,CTE_xml as (
    select Concatenated,CONVERT(xml,' <root> <s>' + REPLACE(Concatenated,',','</s> <s>') + '</s>   </root> ') as Concatenated_xml
     from CTE
)
,CTE_Unpivot as (
    select CTE_xml.Concatenated,
            T.c.value('.','varchar(max)') as Val,
            'Col'+(T.c.value('for $i in . return count(../*[. << $i]) + 1', 'varchar(max)')) as Name
         from CTE_xml
cross APPLY Concatenated_xml.nodes('/root/s')  T(c)
)
select @columnVar = coalesce(@columnVar + ',', '') +  '['+Name+']' from (select distinct name as name from  CTE_Unpivot) ds

select @Query = ';with CTE  as (
        select ''Jhon,Keen,Susan'' as Concatenated
        union all select ''Jims,Huda'' as Concatenated
        union all select ''Boul,Rees'' as Concatenated
        union all select ''Lora'' as Concatenated

        )
        ,CTE_xml as (
            select Concatenated,CONVERT(xml,'' <root> <s>'' + REPLACE(Concatenated,'','',''</s> <s>'') + ''</s>   </root> '') as Concatenated_xml
             from CTE
        )
        ,CTE_Unpivot as (
            select CTE_xml.Concatenated,
                    T.c.value(''.'',''varchar(max)'') as Val,
                    ''Col''+(T.c.value(''for $i in . return count(../*[. << $i]) + 1'', ''varchar(max)'')) as Name
                 from CTE_xml
        cross APPLY Concatenated_xml.nodes(''/root/s'')  T(c)
        )
        select * from 
        (SELECT CTE_Unpivot.Concatenated, CTE_Unpivot.Val,CTE_Unpivot.Name  from CTE_Unpivot) as Src
        pivot (
            max(src.Val)
            for Name in ('+@columnVar+')-- in ([Col1], [Col2], [Col3], [Col4])
        )as pvt'

exec (@Query)

Segregate concatenated values into new separate columns using SQL Server

2 Answers2