Tsql to create columns from comma separated values

  • 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

    12345

    678910

    Please let me know how to proceed.

    Thanks

    Sam

  • 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 Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Thanks will definitely try this out.

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

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