SQL How to Split One Column WIth 2 Different Delimiters into Multiple Variable Columns

Question

I have a very similar issue to the one posted and answered at : SQL How to Split One Column into Multiple Variable Columns

In my case, I have a column of data that is separated with 2 types of delimiters (I know, terrible). Here's some code to start with:

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')

My goal is to use a function or CTE to return those values separately. Ideally, I'd like them returned in separate columns (the number of columns would have to be determined dynamically).

Here's the desired output:

DL1  DL2  DL3  DL4  DL5  DL6
50   60
75   10
115  50   20   10   5 
80   65   40   23   12   10
100

Use REPLACE function to replace one of the delimiters with another, and you will have one delimiter to easily follow other topic from there. — Nenad Zivkovic
– Nenad Zivkovic, Commented Oct 10, 2013 at 14:22

Matt · Accepted Answer · 2013-10-10 15:29:27Z

Here it is with full Dynamic SQL generating the columns dynamically.

;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 )

--Reduce strings to single delimited value each, and store in table
SELECT DelimList,LEFT(String,CHARINDEX(';',String)-1) AS String, Sort
INTO #CleanDelim
FROM Split
WHERE String <> ''
OPTION (MAXRECURSION 10)

--Produce dynamic list of column names and alias for SELECT list
DECLARE @Cols AS VARCHAR(MAX)
SELECT @Cols = STUFF((SELECT DISTINCT '],[' + CAST(Sort AS VARCHAR) + '] AS [DL' + CAST(Sort AS VARCHAR)
FROM #CleanDelim 
ORDER BY '],[' + CAST(Sort AS VARCHAR) + '] AS [DL' + CAST(Sort AS VARCHAR)
FOR XML PATH('')),1,2,'') + ']'

--Produce dynamic list of column names for PIVOT clause
DECLARE @List AS VARCHAR(MAX)
SELECT @List = STUFF((SELECT DISTINCT '],[' + CAST(Sort AS VARCHAR)
FROM #CleanDelim 
ORDER BY '],[' + CAST(Sort AS VARCHAR)
FOR XML PATH('')),1,2,'') + ']'

--Generate dynamic query
DECLARE @SQL AS VARCHAR(MAX)
SELECT @SQL = '
SELECT DelimList,' + @Cols + '
FROM #CleanDelim
PIVOT(MAX(String) FOR Sort IN (' + @list + ')) pvt'
FROM #CleanDelim

--Execute dynamic query
EXEC(@SQL)

SQL Fiddle

Matt · Accepted Answer · 2013-10-10 14:54:33Z

1

I love a good puzzle.

;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

SQL Fiddle

edited Oct 10, 2013 at 14:54

answered Oct 10, 2013 at 14:48

Matt

1,16513 silver badges29 bronze badges

1 Comment

Matt Over a year ago

To have the columns dynamically generated, your only option would be dynamic SQL. Or better yet, use the output from Cleanup, and have your reporting tool produce the columns.

Collectives™ on Stack Overflow

SQL How to Split One Column WIth 2 Different Delimiters into Multiple Variable Columns

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related