CODEWITHSUNDEEP
sqlsql-servert-sql
MacGruber
MacGruber

Reputation: 33

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

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

Upvotes: 0

Views: 2525

Answers (2)

Matt
Matt

Reputation: 1155

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

Upvotes: 1

Matt
Matt

Reputation: 1155

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

Upvotes: 1

Related Questions