Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Tsql to create columns from comma separated values Expand / Collapse
Author
Message
Posted Friday, August 9, 2013 10:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:02 AM
Points: 63, Visits: 562
Hi All,

I have table with single column (Datatype: nvarchar(4000)) and with following records:

1,2,3,4,5
6,7,8,9,10

The no of comma is same accross all the rows..

Now my requirement is to create a table with separate columns for each comma separated values. Here is the output:

Col A: ColB: ColC; ColD; ColE
1 2 3 4 5
6 7 8 9 10


Please let me know how to proceed.

Thanks
Sam
Post #1482849
Posted Friday, August 9, 2013 10:22 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 2:34 PM
Points: 12,923, Visits: 12,342
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.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1482852
Posted Friday, August 9, 2013 8:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:02 AM
Points: 63, Visits: 562
Thanks will definitely try this out.
Post #1482993
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse