Another solution is to use
Common Table Expressions[
^].
Have a look here:
DECLARE @string1 VARCHAR(30)
DECLARE @string2 VARCHAR(30)
DECLARE @id INT
DECLARE @curDate DATETIME
SET @string1 ='30:40:50'
SET @string2 ='23,58,48,60'
SET @id = 101
SET @curDate = GETDATE()
DECLARE @tmp TABLE (ID INT, Val1 INT, Val2 INT, aDate DATETIME)
DECLARE @fv TABLE (ID INT, Val1 INT, aDate DATETIME, RowNo INT)
;WITH firstVal AS
(
SELECT @id AS ID, LEFT(@string1, CHARINDEX(':',@string1)-1) AS Val1, CONVERT(VARCHAR(30),RIGHT(@string1, LEN(@string1)-CHARINDEX(':',@string1))) AS Remainder, 1 AS RowNo
WHERE CHARINDEX(':',@string1)>0
UNION ALL
SELECT @id AS ID, LEFT(Remainder, CHARINDEX(':',Remainder)-1) AS Val1, CONVERT(VARCHAR(30),RIGHT(Remainder, LEN(Remainder)-CHARINDEX(':',Remainder))) AS Remainder, RowNo + 1 AS RowNo
FROM firstVal
WHERE CHARINDEX(':',Remainder)>0
UNION ALL
SELECT @id AS ID, Remainder AS Val1, NULL AS Remainder, RowNo + 1 AS RowNo
FROM firstVal
WHERE CHARINDEX(':',Remainder)=0
)
INSERT INTO @fv (ID, Val1, aDate, RowNo)
SELECT ID, Val1, @curDate AS aDate, RowNo
FROM firstVal
DECLARE @sv TABLE (ID INT, Val2 INT, aDate DATETIME, RowNo INT)
;WITH secondVal AS
(
SELECT @id AS ID, LEFT(@string2, CHARINDEX(',',@string2)-1) AS Val2, CONVERT(VARCHAR(30), RIGHT(@string2, LEN(@string2)-CHARINDEX(',',@string2))) AS Remainder, 1 AS RowNo
WHERE CHARINDEX(',',@string2)>0
UNION ALL
SELECT @id AS ID, LEFT(Remainder, CHARINDEX(',',Remainder)-1) AS Val2, CONVERT(VARCHAR(30), RIGHT(Remainder, LEN(Remainder)-CHARINDEX(',',Remainder))) AS Remainder, RowNo +1 AS RowNo
FROM secondVal
WHERE CHARINDEX(',',Remainder)>0
UNION ALL
SELECT @id AS ID, Remainder AS Val2, NULL AS Remainder, RowNo + 1 AS RowNo
FROM secondVal
WHERE CHARINDEX(',',Remainder)=0
)
INSERT INTO @sv (ID, Val2, aDate, RowNo)
SELECT ID, Val2, @curDate AS aDate, RowNo
FROM secondVal
DECLARE @fvc INT
DECLARE @svc INT
SELECT @fvc = COUNT(ID)
FROM @fv
SELECT @svc = COUNT(ID)
FROM @sv
IF (@fvc > @svc)
BEGIN
INSERT INTO @tmp (ID, Val1, Val2, aDate)
SELECT t1.ID, t1.Val1, t2.Val2, t1.aDate
FROM @fv AS t1 LEFT JOIN @sv AS t2 ON t1.ID = t2.ID AND t1.RowNo = t2.RowNo
END
ELSE
BEGIN
INSERT INTO @tmp (ID, Val1, Val2, aDate)
SELECT t2.ID, t1.Val1, t2.Val2, t2.aDate
FROM @fv AS t1 RIGHT JOIN @sv AS t2 ON t1.ID = t2.ID AND t1.RowNo = t2.RowNo
END
SELECT *
FROM @tmp
Result:
101 30 23 2013-06-26 21:22:02.763
101 40 58 2013-06-26 21:22:02.763
101 50 48 2013-06-26 21:22:02.763
101 NULL 60 2013-06-26 21:22:02.763