将一长逗号字符串拆分为多列新表

我是 SQL 的犹太教徒，请原谅我的无知。我有一张名为“temp”的表，其中包含一个带有一个长逗号分隔字符串的字段，因此：

Field1
Apples,oranges, pears, berries, melons

我想将上述内容插入到具有预定义列的现有表“详细信息”中，因此它看起来像这样：

Field1   Field2   Field3  Field4   Field5
Apples   Oranges  Pears   Berries  Melons 

我一直在阅读论坛，我认为我需要使用一个函数 - 但是我实际上该如何做到这一点？哪个功能最好？有人说 XML 函数最好？

Regards,

Michael

Try this

;WITH Split_Fields (Field, xmlfields)
AS
(
    SELECT Field1 AS Field,
    CONVERT(XML,'<Fields><field>'  
    + REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields
      FROM Table1
)

 SELECT Field,      
 xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1,    
 xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2,
 xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3,    
 xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4,
 xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5
 FROM Split_Fields

SQLFiddle 演示 http://sqlfiddle.com/#!3/38455/1

OR

SELECT Field,
xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1, 
xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2, 
xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3, 
xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4, 
xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5 
FROM 
(
SELECT 
Field1 AS Field
,CONVERT(XML,'<Fields><field>' + REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields 
FROM Table1
) Split_Fields

To 插入另一个表（例如：详细信息）尝试以下

;WITH Split_Fields (Field, xmlfields)
AS
( 
SELECT 
Field1 AS Field
,CONVERT(XML,'<Fields><field>' + REPLACE(Field1,',', '</field><field>') + '</field></Fields>') AS xmlfields 
FROM Table1
) 

INSERT INTO Detail
SELECT 
xmlfields.value('/Fields[1]/field[1]','varchar(100)') AS Field1, 
xmlfields.value('/Fields[1]/field[2]','varchar(100)') AS Field2, 
xmlfields.value('/Fields[1]/field[3]','varchar(100)') AS Field3, 
xmlfields.value('/Fields[1]/field[4]','varchar(100)') AS Field4, 
xmlfields.value('/Fields[1]/field[5]','varchar(100)') AS Field5 
FROM Split_Fields