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
[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