把一个字段内用逗号分隔的内容分成多条记录的sql代码

----> 生成测试数据: @T
DECLARE @T TABLE (id INT,Col1  VARCHAR(50),Col2 VARCHAR(50))
INSERT INTO @T
SELECT 1,'卸货费','运输车队业务, 客服接单业务'
UNION ALL
SELECT 2,'报关费','报关业务、客服接单业务'

----SQL查询如下:
SELECT
    T.id,
    A.x.value('.','varchar(10)') AS code,
    B.x.value('.','varchar(10)') AS code2
FROM (
    SELECT *,doc = CONVERT(xml,'<v>'+REPLACE(code,',','</v><v>')+'</v>'),
         doc2=CONVERT(xml,'<v>'+REPLACE(code2,',','</v><v>')+'</v>')
    FROM @T
) AS T
    CROSS APPLY doc.nodes('//v') AS A(x)
    CROSS APPLY doc2.nodes('//v') AS B(x)

WITH  roy
      AS ( SELECT   Col1 ,
                   COl2 = CAST(LEFT(Col2, CHARINDEX(',', Col2 + ',') - 1) AS NVARCHAR(100)) ,
                   Split = CAST(STUFF(COl2 + ',', 1,
                                    CHARINDEX(',', Col2 + ','), '') AS NVARCHAR(100))
         FROM     Tab
         UNION ALL
         SELECT   Col1 ,
                   COl2 = CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(100)) ,
                    Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(100))
            FROM     Roy
            WHERE    split > ''
             )
    SELECT  COl1 ,
             COl2
     FROM    roy
     ORDER BY COl1
OPTION  ( MAXRECURSION 0 )