Delimiter splitting into separate dynamic columns in SQL Server

Question

I understand that a lot of question related to this are available in Google as well as in SO but the specific question is not available so asking here for the great people's help.

The main task is to split the pipe separated values of a single column into multiple columns (value will be changing so need to create columns dynamically) in SQL Server.

I have gone through multiple links and understand that

1. After 2016, STRING_SPLIT() built-in function will help to split and show in separate rows (but I need to show in columns).

2. [PIVOT] -> PIVOT is possible in both Static and Dynamic one but this can be useful for the data that is in row order to make it Column order. But my table data is in column order with Pipe separation into a single column.

3. XML -> This also static only(See my SQL Fiddle queries and output) and in need dynamic creation of columns without defining number of columns in the query.

Code:

CREATE TABLE DELIMTEDPATH
(
    ID int,
    Path varchar(max)
);

INSERT INTO DELIMTEDPATH 
VALUES (1, 'John|Albert|James'),
       (2, 'Cricket'),
       (3, 'Mary|Joseph|Priyanka|Gilbert|Customer|Service|Passenger|MN-1234|MK-5678');`

;WITH SplitMenus AS
(
    SELECT 
        ID, 
        CONVERT(XML, '<MENUS><Menu>' + REPLACE(Path, '|', '</Menu><Menu>') + '</Menu></MENUS>') AS Path
    FROM
        DELIMTEDPATH
    WHERE
        ID IN (1, 2, 3)
)
SELECT
    ID, 
    Path.value('/MENUS[2]/Menu[2]', 'varchar(100)') AS Name1,
    Path.value('/MENUS[2]/Menu[1]', 'varchar(100)') AS Name2,
    Path.value('/MENUS[2]/Menu[3]', 'varchar(100)') AS Name3,
    Path.value('/MENUS[2]/Menu[4]', 'varchar(100)') AS Name4
FROM
    SplitMenus

I'm getting this output:

But the output I need is dynamic on to show all the values of ID=3.

question from:https://stackoverflow.com/questions/65856069/delimiter-splitting-into-separate-dynamic-columns-in-sql-server

Answer 1

Here is a solution by using XML, XQuery, and dynamic SQL.

SQL

USE tempdb;
GO

-- DDL and sample data population, start
DROP TABLE IF EXISTS DELIMTEDPATH;
CREATE TABLE DELIMTEDPATH (ID INT IDENTITY PRIMARY KEY, [Path] VARCHAR(MAX));

INSERT INTO DELIMTEDPATH ([Path]) VALUES 
('John|Albert|James'),
('Cricket'),
('Mary|Joseph|Priyanka|Gilbert|Customer|Service|Passenger|MN-1234|MK-5678');
-- DDL and sample data population, end

DECLARE @separator CHAR(1) = '|'
    , @CrLf CHAR(2) = CHAR(13) + CHAR(10)
    , @nameCounter INT
    , @i INT = 1;

SET @nameCounter = (SELECT MAX(LEN([Path]) - LEN(REPLACE([Path], @separator,'')))/COALESCE(NULLIF(LEN(@separator), 0), 1) AS cnt
FROM DELIMTEDPATH) + 1;

DECLARE @SQL NVARCHAR(MAX) = 
N';WITH rs AS
(
    SELECT ID, 
        CAST(''<root><r>'' + REPLACE([Path], ''' + @separator + ''', ''</r><r>'') + ''</r></root>'' AS XML) AS xmldata
    FROM DELIMTEDPATH
)
SELECT ID' + @CrLf;

WHILE @i <= @nameCounter BEGIN
    SET @SQL += ', c.value(''(r[' + CAST(@i AS VARCHAR(3)) + ']/text())[1]'', ''VARCHAR(100)'') AS Name' + CAST(@i AS VARCHAR(3)) + @CrLf
    SET @i += 1
END

SET @SQL += 'FROM rs CROSS APPLY xmldata.nodes(''/root'') AS t(c);'

-- just to see it
PRINT @sql;

-- we are ready at this point
EXEC sp_executesql @SQL;

Output

+----+---------+--------+----------+---------+----------+---------+-----------+---------+
| ID |  Name1  | Name2  |  Name3   |  Name4  |  Name5   |  Name6  |   Name7   |  Name8  |
+----+---------+--------+----------+---------+----------+---------+-----------+---------+
|  1 | John    | Albert | James    | NULL    | NULL     | NULL    | NULL      | NULL    |
|  2 | Cricket | NULL   | NULL     | NULL    | NULL     | NULL    | NULL      | NULL    |
|  3 | Mary    | Joseph | Priyanka | Gilbert | Customer | Service | Passenger | MN-1234 |
+----+---------+--------+----------+---------+----------+---------+-----------+---------+