在SQL中经常会遇到按固定格式拼接的字符串,有时候需要将这些字符串拆分为多行。
经网上查找,找到方法:(SQL2005+)
将字符串转换为XML格式,再通过MSSQL对XML的处理分成多行。
CREATE TABLE tb (id INT, strArr VARCHAR(30));
GO
INSERT INTO tb VALUES(1, 'aa,bb');
INSERT INTO tb VALUES(2, 'aaa,bbb,ccc');
GO
SELECT * FROM tb;
GO
-- 一行转多行查询
SELECT A.id, B.strVal
FROM(
-- 拼接为XML字符串,转换为XML格式数据 <r>是根节点 <v>是值节点
SELECT id, strXML=CONVERT(XML, '<r><v>'+REPLACE(strArr, ',', '</v><v>')+'</v></r>')FROM tb
) A
OUTER APPLY(
-- 读取XML的值节点的值,转为字符串格式
SELECT strVal=N.v.value('.', 'varchar(100)') FROM A.strXML.nodes('/r/v') N(v)
) B;
GO
DROP TABLE tb;
GO
一行转多行。参考资料:
https://bbs.csdn.net/topics/392362532?page=1&from=singlemessage
OUTER APPLY 交叉连接查询。参考资料:
https://blog.csdn.net/wikey_zhang/article/details/77480118
读取XML节点值,参考资料:
文章评论