Sql将一列数据拆分为多行显示的两种方法
原始数据与期望结果
有表tb, 如下:
id value
----------- -----------
1 aa,bb
2 aaa,bbb,ccc
欲按id,分拆value列, 分拆后结果如下:
id value
----------- --------
1 aa
1 bb
2 aaa
2 bbb
2 ccc
方法一
create table tb(id int,value varchar(30)) insert into tb values(1,'aa,bb') insert into tb values(2,'aaa,bbb,ccc') go SELECT *FROM tb SELECT A.id, B.value FROM( SELECT id, [value] = CONVERT(xml,'<root><v>' + REPLACE([value], ',', '</v><v>') + '</v></root>') FROM tb )A OUTER APPLY( SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v) )B DROP TABLE tb
方法二
USE tempdb GO --1. 要增加一个分割表值函数 IF OBJECT_ID('[dbo].[Fun_String2ToStringArray]') IS NOT NULL DROP FUNCTION [dbo].[Fun_String2ToStringArray] GO CREATE FUNCTION [dbo].[Fun_String2ToStringArray](@str NVARCHAR(MAX), @split NVARCHAR(10)) RETURNS @table TABLE ([item] NVARCHAR(max)) AS BEGIN IF LEN(@split) = 0 BEGIN SET @split = N',' END DECLARE @xml XML; SET @xml = CONVERT(XML, '<x><![CDATA[' + replace(CONVERT(VARCHAR(MAX), @str), @split, ']]></x><x><![CDATA[') + ']]></x>') INSERT INTO @table SELECT item FROM (SELECT c.value('text()[1]', 'nvarchar(4000)') [item] FROM @xml.nodes('/x') t(c)) t WHERE item IS NOT NULL RETURN END GO --测试表及测试数据 IF OBJECT_ID('t') IS NOT NULL DROP TABLE t GO CREATE TABLE t( result VARCHAR(20), d DATETIME ) GO INSERT INTO t VALUES('23@34','2018-04-01 08:00') INSERT INTO t VALUES('3','2018-04-01 09:00') INSERT INTO t VALUES('3','2018-04-02 09:00') INSERT INTO t VALUES('3@4.6@5.7@3.2@4','2018-04-03 08:00') INSERT INTO t VALUES('30','2018-04-04 09:00') INSERT INTO t VALUES('34','2018-04-05 09:00') INSERT INTO t VALUES('6@4.6@4.7@3.2@3','2018-04-06 09:00') INSERT INTO t VALUES('8','2018-04-07 09:00') INSERT INTO t VALUES('8','2018-04-08 09:00') --实际的查询语句 ;WITH cte AS ( SELECT f.item AS result,t.d FROM t CROSS APPLY dbo.[Fun_String2ToStringArray](t.result,'@') AS f ) ,cte2 AS( SELECT TOP 125 * FROM cte ORDER BY d ),cte3 AS( SELECT TOP 125 * FROM cte ORDER BY d DESC ) SELECT * FROM cte2 UNION SELECT * FROM cte3
摘录于此贴