[转载]SQL 将一个字段内用逗号分隔的内容分成多条记录 – 岩子 – 博客园.
由于业务需求,我们可能会把一串以分割符字符串数据放到一个字段,如我们在客户端处理拆分是很简单的,不过这样做效果不太好,怎么用SQL SERVER 2008 来解决这件事件哪?
方案1
用SQL SERVER XML 功能来解决
----> 生成测试数据: @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, '' + REPLACE (code, ',' , '' )+ '' ), doc2= CONVERT (xml, '' + REPLACE (code2, ',' , '' )+ '' ) FROM @T ) AS T CROSS APPLY doc.nodes( '//v' ) AS A(x) CROSS APPLY doc2.nodes( '//v' ) AS B(x) |
方案2
用SQL SERVER WITH 递归查询
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 ) |