1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43
|
WITH CTE AS (
SELECT ID,
split.a.value('.', 'NVARCHAR(MAX)') [Operations],
ROW_NUMBER() OVER(ORDER BY ( SELECT 1)) RN
FROM
(
SELECT ID,
CAST('<A>' + REPLACE(OperationList, ';', '</A><A>') + '</A>' AS XML) AS [Operations]
FROM dbo.TLog
) a
CROSS APPLY Operations.nodes('/A') AS split(a)),
CTE1 AS (
SELECT ID,
split.a.value('.', 'NVARCHAR(MAX)') [Dates],
ROW_NUMBER() OVER(ORDER BY ( SELECT 1 )) RN
FROM
(
SELECT ID,
CAST('<A>' + REPLACE(DateList, ';', '</A><A>') + '</A>' AS XML) AS [Dates]
FROM dbo.TLog
) aa
CROSS APPLY Dates.nodes('/A') AS split(a)),
CTE2 AS (
SELECT ID,
split.a.value('.', 'NVARCHAR(MAX)') [Amounts],
ROW_NUMBER() OVER(ORDER BY ( SELECT 1 )) RN
FROM
(
SELECT ID,
CAST('<A>' + REPLACE(AmountList, ';', '</A><A>') + '</A>' AS XML) AS [Amounts]
FROM dbo.TLog
) aaa
SELECT
C.ID
,LTRIM(RTRIM(C.Operations))
,LTRIM(RTRIM(C1.Dates))
,LTRIM(RTRIM(C1.Amounts))
FROM CTE C
JOIN CTE1 C1 ON C1.RN = C.RN
JOIN CTE2 C2 ON C2.RN = C.RN
WHERE C.Operations != '' AND C1.Dates != '' AND C2.Amounts != ''; |
Partager