CODEWITHSUNDEEP
sql-serversql-server-2008-r2
GPH
GPH

Reputation: 1897

Using Split Function to restructure a table SQL

I'm currently trying to learn about split functions and how to use one practically. I have a table which I need to split out and think i need to use the split (hence trying to learn). however i'm confused as to how to get it working!

Using excel to as a mock up the 1st set of data is how it is. The 2nd set is how i want it to be after. enter image description here

Can anyone help?

Upvotes: 2

Views: 271

Answers (2)

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

Use a string split function against each of the columns and combine the result using union all followed by a pivot to retrieve your values in a proper fashion.

select P.SaveName, P.Para1, P.Para2, P.Para3, P.Para4, P.Para5, P.Para6
from (
     select T.SaveName, S.ID, S.Item, S.Para
     from YourTable as T 
       cross apply (select ID, Item, 'Para1' from dbo.SplitString(T.Para1, ',') union all
                    select ID, Item, 'Para2' from dbo.SplitString(T.Para2, ',') union all
                    select ID, Item, 'Para3' from dbo.SplitString(T.Para3, ',') union all
                    select ID, Item, 'Para4' from dbo.SplitString(T.Para4, ',') union all
                    select ID, Item, 'Para5' from dbo.SplitString(T.Para5, ',') union all
                    select ID, Item, 'Para6' from dbo.SplitString(T.Para6, ',')
                   ) as S(ID, Item, Para)
     ) as C
pivot (min(C.Item) for C.Para in (Para1, Para2, Para3, Para4, Para5, Para6)) as P
order by P.SaveName, P.ID

SQL Fiddle

Upvotes: 3

Taryn
Taryn

Reputation: 247810

As an alternative to using a split function, you can split the data using a recursive common-table-expression. 

;with cte as
(
  select SaveName,
    cast(left(Para1, charindex(',',Para1+',')-1) as varchar(50)) para1,
    stuff(Para1, 1, charindex(',',Para1+','), '') para1list,
    cast(left(Para2, charindex(',',Para2+',')-1) as varchar(50)) para2,
    stuff(Para2, 1, charindex(',',Para2+','), '') para2list,
    cast(left(Para3, charindex(',',Para3+',')-1) as varchar(50)) para3,
    stuff(Para3, 1, charindex(',',Para3+','), '') para3list,
    cast(left(Para4, charindex(',',Para4+',')-1) as varchar(50)) para4,
    stuff(Para4, 1, charindex(',',Para4+','), '') para4list,
    cast(left(Para5, charindex(',',Para5+',')-1) as varchar(50)) para5,
    stuff(Para5, 1, charindex(',',Para5+','), '') para5list,
    cast(left(Para6, charindex(',',Para6+',')-1) as varchar(50)) para6,
    stuff(Para6, 1, charindex(',',Para6+','), '') para6list
  from yourtable
  union all
  select SaveName,
    cast(left(para1list, charindex(',',para1list+',')-1) as varchar(50)) para1,
    stuff(para1list, 1, charindex(',',para1list+','), '') para1list,
    cast(left(Para2list, charindex(',',Para2list+',')-1) as varchar(50)) para2,
    stuff(Para2list, 1, charindex(',',Para2list+','), '') para2list,
    cast(left(Para3list, charindex(',',Para3list+',')-1) as varchar(50)) para3,
    stuff(Para3list, 1, charindex(',',Para3list+','), '') para3list,
    cast(left(Para4list, charindex(',',Para4list+',')-1) as varchar(50)) para4,
    stuff(Para4list, 1, charindex(',',Para4list+','), '') para4list,
    cast(left(Para5list, charindex(',',Para5list+',')-1) as varchar(50)) para5,
    stuff(Para5list, 1, charindex(',',Para5list+','), '') para5list,
    cast(left(Para6list, charindex(',',Para6list+',')-1) as varchar(50)) para6,
    stuff(Para6list, 1, charindex(',',Para6list+','), '') para6list
  from cte
  where para1list > ''
    or para2list > ''
    or para3list > ''
    or para4list > ''
    or para5list > ''
    or para6list > ''
) 
select SaveName, para1, para2, para3, para4, para5, para6
from cte
order by savename;

See SQL Fiddle with Demo

Upvotes: 3

Related Questions