SQL將一個字段內(nèi)用逗號分隔的內(nèi)容分成多條記錄

方案1

  用SQL SERVER XML 功能來解決

1 ----> 生成測試數(shù)據(jù): @T
2  DECLARE @T TABLE (id INT,Col1  VARCHAR(50),Col2 VARCHAR(50))
3  INSERT INTO @T
4  SELECT 1,'卸貨費','運輸車隊業(yè)務(wù), 客服接單業(yè)務(wù)' 
5  UNION ALL
6  SELECT 2,'報關(guān)費','報關(guān)業(yè)務(wù)、客服接單業(yè)務(wù)'

----SQL查詢?nèi)缦?

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)

方案2 

用SQL SERVER WITH 遞歸查詢

 1 WITH    roy
 2           AS ( SELECT   Col1 ,
 3                         COl2 = CAST(LEFT(Col2, CHARINDEX(',', Col2 + ',') - 1) AS NVARCHAR(100)) ,
 4                         Split = CAST(STUFF(COl2 + ',', 1,
 5                                            CHARINDEX(',', Col2 + ','), '') AS NVARCHAR(100))
 6                FROM     Tab
 7                UNION ALL
 8                SELECT   Col1 ,
 9                         COl2 = CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(100)) ,
10                         Split = CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(100))
11                FROM     Roy
12                WHERE    split > ''
13              )
14     SELECT  COl1 ,
15             COl2
16     FROM    roy
17     ORDER BY COl1
18 OPTION  ( MAXRECURSION 0 )

生活不易，碼農(nóng)辛苦
如果您覺得本網(wǎng)站對您的學(xué)習(xí)有所幫助,可以手機掃描二維碼進行捐贈