Tsql to create columns from comma separated values

Question

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

This will do it.

if OBJECT_ID('tempdb..#Something') is not null

drop table #Something

create table #Something

(

SomeID int identity,

SomeValue varchar(25)

)

insert #Something

select '1,2,3,4,5' union all

select '6,7,8,9,10'

select MAX(Case when ItemNumber = 1 then Item else null end) as ColA,

MAX(Case when ItemNumber = 2 then Item else null end) as ColB,

MAX(Case when ItemNumber = 3 then Item else null end) as ColC,

MAX(Case when ItemNumber = 4 then Item else null end) as ColD,

MAX(Case when ItemNumber = 5 then Item else null end) as ColE

from #Something s

cross apply dbo.DelimitedSplit8K(s.SomeValue, ',') x

group by SomeID

You can find the code for the DelimitedSplit8K function by following the article in my signature about splitting strings.

Notice how I posted ddl and sample data? You should do something like that in the future.

Then when you parse this stuff out you should store your data normalized. Storing multiple values in a single column violates first normal form.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

sam 55243 SSCrazy Points: 2697 More actions · Answer 2

SSCrazy

Points: 2697

August 9, 2013 at 8:17 pm

Thanks will definitely try this out.