Split Two Comma Delimited Columns with T-SQL


  

 create table test (Name varchar(20), value varchar(20) )
insert into test values('A,B,C','1,2,3')

--===== Create number table on-the-fly
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n<101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y),
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)

,NameCte as (
select    substring(Name, n, charindex(',', Name + ',', n) - n)  splitName
from test
cross apply Nums
Where n <= len(Name) AND substring(',' + Name, n, 1) = ','

)
,ValueCTE as 
(
select      substring(value, n, charindex(',', value + ',', n) - n)  splitVal
from test
cross apply Nums
Where n <= len(Name) AND substring(',' + Name, n, 1) = ','
)

Select splitName+splitVal from NameCte,ValueCTE

drop table test

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s