Splitting a column value in to many [modified]

[rakeshs312]

Hi, I have a table and one column is there like this SKU Value ----------- MKG Ex Du A918731765L TVN Ex Du A919721354L NCL Ex Du A901713191L Bin Ex Du A948606458L --- -- -- ---------- --- -- -- ---------- etc etc etc(table is having hundreds of data like this and it is a expanding table.So i have to split all of them not only the mentioned data.So a common way to split this column is required.) I am writing a stored procedure to get this columns as SK1 SK2 SK3 SK4 SK5 --------------------------------------------------- MKG EX Du A91873176 5L TVN Ex Du A91972135 4L etc etc The values will be always in the above format. Please help. Thanks in advance -- Modified Monday, October 18, 2010 12:20 AM

[RyanEK]

Hi, I suggest you create a split function

CREATE FUNCTION [dbo].[Split]
(
@delimited nvarchar(max),
@delimiter nvarchar(100)
) RETURNS @t TABLE
(
id int identity(1,1),
val nvarchar(max)
)
AS
BEGIN
declare @xml xml
set @xml = N'<root><r>' + replace(@delimited,@delimiter,'</r><r>') + '</r></root>'

insert into @t(val)
select
r.value('.','varchar(15)') as item
from @xml.nodes('//root/r') as records(r)

RETURN
END

Then you cross apply to your data table, pivot, then manipulate the display data. Here is an example:

declare @tmp table (data varchar(50))
insert into @tmp (data) values ('MKG Ex Du A918731765L')
insert into @tmp (data) values ('TVN Ex Du A919721354L')
insert into @tmp (data) values ('NCL Ex Du A901713191L')
insert into @tmp (data) values ('Bin Ex Du A948606458L')

select [1] as 'SK1', [2] as 'SK2', [3] as 'SK3', substring([4], 0, 10) as 'SK3', substring([4], 10, 2) as 'SK4'
from
(
select [1] , [2] , [3] , [4]
from (
select * from
@tmp t
cross apply dbo.Split(t.data,' ')
) as sourcetb
pivot
(max(val) for id in ([1],[2],[3],[4])
) as pivottable
) as t

Ryan

[Mycroft Holmes]

Now that is what I call sending the codz - deserves 5

Never underestimate the power of human stupidity RAH

[rakeshs312]

thanks a lot Ryan