Wednesday, December 4, 2013

spilt more than one value

CREATE TABLE #temp(
[Day] [varchar](500) NULL,
[RawKey] [int] NULL,
[TableID] [int] NULL,
[FromTime] [varchar](500) NULL
)
insert into #temp
select 'Sunday,Monday,Tuesday,Wednesday,Thursday,Friday,Saturday', 1, 1, '10:00,11:00,12:00,13:00,14:00,15:00,16:00,17:00'


;WITH CTE1 AS
(
SELECT *
,RN= Row_Number() OVER( Partition BY [Day],[FromTime],RawKey,TableID ORDER BY TableID)
FROM
(
SELECT *
,DelimitedStringXML = CAST('<d>'+REPLACE([Day],',','</d><d>')+'</d>' AS XML)
,DelimitedValueXML = CAST('<d>'+REPLACE([FromTime],',','</d><d>')+'</d>' AS XML)

FROM #temp
) as t
Cross Apply
(
SELECT y.value('.', 'VARCHAR(30)') AS SplitString FROM DelimitedStringXML.nodes('//d') as  x(y)

) as b
)
,CTE2 AS
(
SELECT *
,RN= Row_Number() OVER( Partition BY [Day],[FromTime],RawKey,TableID ORDER BY TableID)
FROM
(
SELECT *
,DelimitedStringXML = CAST('<d>'+REPLACE([Day],',','</d><d>')+'</d>' AS XML)
,DelimitedValueXML = CAST('<d>'+REPLACE([FromTime],',','</d><d>')+'</d>' AS XML)

FROM #temp
) as t
CROSS APPLY
(
SELECT h.value('.', 'VARCHAR(30)') AS SplitValue FROM DelimitedValueXML.nodes('//d') as  g(h)

) as c
)


SELECT a.RawKey,a.SplitString,b.SplitValue
FROM CTE1 as a
INNER JOIN CTE2 as b
on  a.TableID= b.TableID
AND a.RN = b.RN

drop table #temp

No comments:

Post a Comment