将csv字段拆分为多列 [英] split csv field into multiple columns

问题描述

我有一个包含200,000行数据的表。现场参加者已以csv格式填充；用逗号分隔符分隔。我想将此字段最多分为7个不同的列，分别标记为Field1，Field2等。

I have a table with 200,000 rows of data. The field attendees has been populated in a csv format; split with a comma delimiter. I would like split this field into up to 7 different columns, labelled Field1, Field2 etc.

pkEventBooking  Attendees
166935          p1193,c21867,c21827,c21963,c18069,c19222,
195867          p1193,c21827,c22572,c19222,c22573,c21963,c18069,

新格式

New Format

pkEventBooking   Field1   Field2   Field3    Field 4   Field5   Field6  Field7
166935           p1193    c21867   c21827    c21963    c18069   c19222
195867           p1193    c21827   c22572    c19222    c22573   c21963  c18069,

推荐答案

测试数据

Test Data

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')

查询

Query

;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

结果

Result

╔════════╦════════════╦════════════╦════════════╦════════════╦════════════╦════════════╦════════════╗
║ Value  ║ Attendees1 ║ Attendees2 ║ Attendees3 ║ Attendees4 ║ Attendees5 ║ Attendees6 ║ Attendees7 ║
╠════════╬════════════╬════════════╬════════════╬════════════╬════════════╬════════════╬════════════╣
║ 166935 ║ p1193      ║ c21867     ║ c21827     ║ c21963     ║ c18069     ║ c19222     ║ NULL       ║
║ 195867 ║ p1193      ║ c21827     ║ c22572     ║ c19222     ║ c22573     ║ c21963     ║ c18069     ║
╚════════╩════════════╩════════════╩════════════╩════════════╩════════════╩════════════╩════════════╝

这篇关于将csv字段拆分为多列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！