create table #MessyDelim
(DelimList varchar(255));
insert into #MessyDelim
Values ('30;120;100')
, ('50;60')
, ('75/10')
, ('115/50/20/10/5')
, ('80;65;40;23;12;10')
, ('100')
;WITH
MessyDelim AS --As Nenad Zivkovic suggested, standardize on one delimiter for simplicity
( SELECT DelimList,REPLACE(DelimList,'/',';') AS String
FROM #MessyDelim),
Split AS --Recursive CTE to produce strings each with one less delimited value attached
( SELECT DelimList, 1 AS Sort, String + ';' AS String
FROM MessyDelim
UNION ALL
SELECT DelimList, Sort+1, RIGHT(String,LEN(String)-CHARINDEX(';',String))
FROM Split
WHERE CHARINDEX(';',String) > 0 ),
Cleanup AS --Reduce strings to single delimited value each
( SELECT DelimList,LEFT(String,CHARINDEX(';',String)-1) AS String, Sort
FROM Split
WHERE String <> '' )
SELECT DelimList, --Pivot out into columns
[1] AS DL1,
[2] AS DL2,
[3] AS DL3,
[4] AS DL4,
[5] AS DL5,
[6] AS DL6
FROM Cleanup
PIVOT(MAX(String) FOR Sort IN ([1],[2],[3],[4],[5],[6])) pvt
ORDER BY DelimList
OPTION (MAXRECURSION 10) --Just for safety sake
(DelimList varchar(255));
insert into #MessyDelim
Values ('30;120;100')
, ('50;60')
, ('75/10')
, ('115/50/20/10/5')
, ('80;65;40;23;12;10')
, ('100')
;WITH
MessyDelim AS --As Nenad Zivkovic suggested, standardize on one delimiter for simplicity
( SELECT DelimList,REPLACE(DelimList,'/',';') AS String
FROM #MessyDelim),
Split AS --Recursive CTE to produce strings each with one less delimited value attached
( SELECT DelimList, 1 AS Sort, String + ';' AS String
FROM MessyDelim
UNION ALL
SELECT DelimList, Sort+1, RIGHT(String,LEN(String)-CHARINDEX(';',String))
FROM Split
WHERE CHARINDEX(';',String) > 0 ),
Cleanup AS --Reduce strings to single delimited value each
( SELECT DelimList,LEFT(String,CHARINDEX(';',String)-1) AS String, Sort
FROM Split
WHERE String <> '' )
SELECT DelimList, --Pivot out into columns
[1] AS DL1,
[2] AS DL2,
[3] AS DL3,
[4] AS DL4,
[5] AS DL5,
[6] AS DL6
FROM Cleanup
PIVOT(MAX(String) FOR Sort IN ([1],[2],[3],[4],[5],[6])) pvt
ORDER BY DelimList
OPTION (MAXRECURSION 10) --Just for safety sake
No comments:
Post a Comment