Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
menu search
person
Welcome To Ask or Share your Answers For Others

Categories

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:

enter image description here

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


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
2.2k views
Welcome To Ask or Share your Answers For Others

1 Answer

等待大神解答

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
thumb_up_alt 0 like thumb_down_alt 0 dislike
Welcome to ShenZhenJia Knowledge Sharing Community for programmer and developer-Open, Learning and Share
...