| Author |
 Topic  |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
 Posted - 04/27/2006 : 05:55:14
|
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 |
|
|
RyanRandall
Flowing Fount of Yak Knowledge
United Kingdom
1074 Posts |
Posted - 04/27/2006 : 06:53:10
|
Nice work Madhivanan - very neat 
Here's a link (for others) to the usual alternatives to this sort of problem...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=CSV,Splitting%20delimited%20lists,Best%20split%20functions
And here's one of those techniques applied to this example (adjusted slightly)...
--data
declare @DeNormalisedTable table(data varchar(8000), Code int, FirstName varchar(100), LastName varchar(100))
insert into @DeNormalisedTable (data)
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'
--calculation
declare @i int, @j int, @k int
update @DeNormalisedTable set
@i = charindex(',', data), Code = left(data, @i-1),
@j = charindex(',', data + ',', @i+1), FirstName = substring(data, @i+1, @j-@i-1),
@k = charindex(',', data + ',', @j+1), LastName = substring(data, @j+1, @k-@j-1)
select * from @DeNormalisedTable
Ryan Randall
www.monsoonmalabar.com London-based IT consultancy
Solutions are easy. Understanding the problem, now, that's the hard part. |
 |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 04/27/2006 : 09:08:45
|
Thanks Ryan. Your method is more simpler than mine
Madhivanan
Failing to plan is Planning to fail |
|
|
|
spirit1
Cybernetic Yak Master
Slovenia
11741 Posts |
|
|
madhivanan
Premature Yak Congratulator
India
22461 Posts |
Posted - 04/27/2006 : 11:03:45
|
Thanks Mladen. Both of yours and Ryan's are more effecient
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
|
|
CSV to Multiple Columns
