CSV to Multiple Columns
If the table is denormalised and has Comma Seperate Values in acolumn, this code will copy it to Multiple columns of aNormalised 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