Sql Server数据拆分：将一行数据拆分成多条记录

假设在一个表中，name列中有许多人名，人名间以逗号分隔，现在需要将name字段中的名字拆分开，每个名字成为一条记录。

一、创建表
创建表如下：
CREATE TABLE [dbo].[tb_0001](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [name] [nvarchar](100) NOT NULL
) ON [PRIMARY]
GO

添加数据
INSERT INTO [dbo].[tb_0001]([name])VALUES('张三')
INSERT INTO [dbo].[tb_0001]([name])VALUES('李四，王五')
INSERT INTO [dbo].[tb_0001]([name])VALUES('赵六，小七，大八，憨九')
INSERT INTO [dbo].[tb_0001]([name])VALUES('老十，老十一，老十二')
INSERT INTO [dbo].[tb_0001]([name])VALUES('老十四，老十五，老十六')

二、实现方法
方法1：
1.首先需要自定义一个切分函数
CREATE FUNCTION [dbo].[MY_SPLIT](@sourceStr NVARCHAR(MAX),@separator NVARCHAR(100))    
RETURNS  @tmp TABLE(        
    SPLIT_ID INT IDENTITY PRIMARY KEY,      
    SPLIT_STR NVARCHAR(MAX)    
)    
AS   
BEGIN   
    DECLARE @short_str NVARCHAR(MAX),@split_str_length int,@split_str_Position_Begin int
    SET @split_str_length = LEN(@separator) 
    SET @sourceStr=REPLACE(REPLACE(@sourceStr,CHAR(10),''),CHAR(13),'')
    IF CHARINDEX(@separator,@sourceStr)=1 
         SET @sourceStr=STUFF(@sourceStr,1,@split_str_length,'')
    IF CHARINDEX(@separator,@sourceStr)=0
        INSERT INTO @tmp SELECT @sourceStr 
    ELSE
        BEGIN
            WHILE 1>0    
                BEGIN   
                    SET @split_str_Position_Begin = CHARINDEX(@separator,@sourceStr)
                    SET @short_str=LEFT(@sourceStr,@split_str_Position_Begin-1) 
                    IF @short_str<>'' INSERT INTO @tmp SELECT @short_str  
                    SET @sourceStr=STUFF(@sourceStr,1,@split_str_Position_Begin+@split_str_length-1,'')
                    SET @split_str_Position_Begin = CHARINDEX(@separator,@sourceStr)
                    IF @split_str_Position_Begin=0 
                    BEGIN
                        IF LTRIM(@sourceStr)<>''
                            INSERT INTO @tmp SELECT @sourceStr 
                        BREAK
                    END
                END           
        END
    RETURN     
END

2，然后执行下面的SQL语句
SELECT SPLIT_ID,SPLIT_STR
FROM  dbo.tb_0001 AS t1 CROSS apply dbo.MY_SPLIT(t1.name,',') AS t2

可以看到字段完成拆分，如下图：

方法2：
SqlServer支持XML数据格式，可以将name字段，转换成XML格式，其原理与方法一的不同。
SELECT 
    tb01.id
   ,tb02.VALUE 
FROM
    ( SELECT id,[value] = CONVERT(xml,'<root><v>' + REPLACE(name, ',', '</v><v>') + '</v></root>') FROM dbo.tb_0001 ) AS tb01  
    OUTER APPLY
    ( SELECT VALUE = N.v.value('.', 'varchar(100)') FROM tb01.[value].nodes('/root/v') N(v) ) AS tb02