SQL如何将一列的数据进行拆分,并分为多行显示

时间:2023-02-07 10:27:18
各位大神,求教:
SQL如何将一列的数据进行拆分,并分为多行显示

转化后的数据:

其中红色标出的是之前的Result拆分后的值,Result一列为@隔开的数据,最多5个数据,个数不定
SQL如何将一列的数据进行拆分,并分为多行显示

将数据转化之后,再按照时间排序,取从开始时间的125行数据和结束时间之前的125条数据

5 个解决方案

#1


该回复于2018-04-19 16:15:48被管理员删除

#2


/*
有表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

#3


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

#4


跟你转发的这个不一样那,

#5


大神们那  感谢感谢

#1


该回复于2018-04-19 16:15:48被管理员删除

#2


/*
有表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

#3


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

#4


跟你转发的这个不一样那,

#5


大神们那  感谢感谢