更新时间:2023-01-20 20:30:16
DECLARE @TABLE TABLE (pkEventBooking INT, Attendees NVARCHAR(MAX))
INSERT INTO @TABLE VALUES
(166935 , 'p1193,c21867,c21827,c21963,c18069,c19222'),
(195867 , 'p1193,c21827,c22572,c19222,c22573,c21963,c18069')
;WITH Split_Names (pkEventBooking, Attendees)
AS
(
SELECT pkEventBooking,
CONVERT(XML,'<Attendees><Attendee>'
+ REPLACE(Attendees,',', '</Attendee><Attendee>') + '</Attendee></Attendees>') AS Attendees
FROM @Table
)
SELECT pkEventBooking,
Attendees.value('/Attendees[1]/Attendee[1]','varchar(100)') AS Attendees1,
Attendees.value('/Attendees[1]/Attendee[2]','varchar(100)') AS Attendees2,
Attendees.value('/Attendees[1]/Attendee[3]','varchar(100)') AS Attendees3,
Attendees.value('/Attendees[1]/Attendee[4]','varchar(100)') AS Attendees4,
Attendees.value('/Attendees[1]/Attendee[5]','varchar(100)') AS Attendees5,
Attendees.value('/Attendees[1]/Attendee[6]','varchar(100)') AS Attendees6,
Attendees.value('/Attendees[1]/Attendee[7]','varchar(100)') AS Attendees7
FROM Split_Names
╔════════╦════════════╦════════════╦════════════╦════════════╦════════════╦════════════╦════════════╗
║ Value ║ Attendees1 ║ Attendees2 ║ Attendees3 ║ Attendees4 ║ Attendees5 ║ Attendees6 ║ Attendees7 ║
╠════════╬════════════╬════════════╬════════════╬════════════╬════════════╬════════════╬════════════╣
║ 166935 ║ p1193 ║ c21867 ║ c21827 ║ c21963 ║ c18069 ║ c19222 ║ NULL ║
║ 195867 ║ p1193 ║ c21827 ║ c22572 ║ c19222 ║ c22573 ║ c21963 ║ c18069 ║
╚════════╩════════════╩════════════╩════════════╩════════════╩════════════╩════════════╩════════════╝