SQL Server Performance

CSV to Multiple Columns

Discussion in 'Contribute Your SQL Server Scripts' started by Madhivanan, Apr 27, 2006.

  1. Madhivanan Moderator


    If the table is denormalised and has Comma Seperate Values in a column, this code will copy it to Multiple columns of a Normalised table





    declare @DeNormalisedTable table(data varchar(8000))

    insert into @DeNormalisedTable
    select '1,Davolio,Nancy' union all
    select '2,Fuller,Andrew' union all
    select '3,Leverling,Janet' union all
    select '4,Peacock,Margaret' union all
    select '5,Buchanan,Steven' union all
    select '6,Suyama,Michael' union all
    select '7,King,Robert' union all
    select '8,Callahan,Laura' union all
    select '9,Dodsworth,Anne'

    select * from @DeNormalisedTable -- Comma Seperated Values

    declare @s varchar(8000), @data varchar(8000)
    Create table #NormalisedTable (Code int, FirstName varchar(100), LastName varchar(100))

    select @s=''

    while exists (Select * from @DeNormalisedTable where data>@s)
    Begin
    Select @s=min(data) from @DeNormalisedTable where data>@s
    select @data=''''+replace(@s,',',''',''')+''''
    insert into #NormalisedTable
    exec('select '+@data)
    End

    select * from #NormalisedTable -- Data in Normalised Table

    drop table #NormalisedTable


    Madhivanan

    Failing to plan is Planning to fail

Share This Page