Delimiter splitting into separate dynamic columns in sql server

Question

I understand that a looot 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() build-in function will help to split and show in separate ROWs (But I need to show in Columns).

2. PIVOT -> It will be based on the static fields

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.

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

Output i'm getting:

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

Answer 1

等待大神解答