Delimiter splitting into separate dynamic columns in SQL Server

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.

https://stackoverflow.com/questions/65856069/delimiter-splitting-into-separate-dynamic-columns-in-sql-server January 23, 2021 at 01:32PM