Question

sqlserver2008 字符分割的字段怎么分成列

Answer 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,'<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)