I have the following string
x
'06/30/2020;58044.373;.001;12/31/2020;58042.373;.003;06/30/2021;78044.373;.007'
I need to transform it into :
col 1 col 2 col 3
---- ----- -----
06/30/2020 58044.373 .001
12/31/2020 58042.373 .003
06/30/2021 78044.373 .007
I can use only select statmets, no cursors or SP’s .
Any help please?
Advertisement
Answer
Workaround with STRING_SPLIT
(it is not safe):
WITH cte AS (
SELECT c, value,
ROW_NUMBER() OVER(ORDER BY (SELECT 1)) % 3 AS rn,
(ROW_NUMBER() OVER(ORDER BY (SELECT 1))-1) / 3 AS grp
FROM t
CROSS APPLY STRING_SPLIT(c, ';')
)
SELECT MAX(CASE WHEN rn = 1 THEN value END) AS col1,
MAX(CASE WHEN rn = 2 THEN value END) AS col2,
MAX(CASE WHEN rn = 0 THEN value END) AS col3
FROM cte
GROUP BY c,grp;