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
After 2016, STRING_SPLIT() build-in function will help to split and show in separate ROWs (But I need to show in Columns).
PIVOT -> It will be based on the static fields
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.
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…