SQL >>将一列中多个不同的值放入一行的一个单元格

最新推荐文章于 2023-05-30 10:19:26 发布

转载最新推荐文章于 2023-05-30 10:19:26 发布 · 545 阅读

sql 专栏收录该内容

12 篇文章

订阅专栏

本文介绍两种SQL查询方法，用于将表格数据从行格式转换为列格式，包括使用FORXML PATH和sys_connect_by_path函数的方法，适用于需要进行数据格式转换的场景。

SQL查询语句：将一列中多个不同的值放入一行的一个单元格里

方法一：使用FOR XML PATH

1.Init

use Test
create table verticalTable
(Name nvarchar(50),
Course nvarchar(50),
Grade int
)
go
alter table dbo.horizontalTable add Name nvarchar(50)

insert into verticalTable values('张三','语文',75)
insert into verticalTable values('张三','数学',85)
insert into verticalTable values('张三','英语',95)
insert into verticalTable values('李四','语文',55)
insert into verticalTable values('李四','数学',90)

2.sql

SELECT Name,
Course=STUFF((SELECT ','+[Course] FROM verticalTable a WHERE a.Name=b.Name FOR XML PATH('')), 1, 1, ''),
Grade=STUFF((SELECT ','+cast(Grade as varchar) FROM verticalTable a WHERE a.Name=b.Name FOR XML PATH('')), 1, 1, '')
FROM verticalTable b
GROUP BY Name

GO
select * FROM verticalTable

3.显示结果：

方法二：

下面是网上摘录，有点繁琐：
表结构和数据如下（表名Test）：
NO VALUE NAME
1 a 测试1
1 b 测试2
1 c 测试3
1 d 测试4
2 e 测试5
4 f 测试6
4 g 测试7
Sql语句：
select No,
ltrim(max(sys_connect_by_path(Value, ';')), ';') as Value,
ltrim(max(sys_connect_by_path(Name, ';')), ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
group by No;
检索结果如下：
NO VALUE NAME
1 a;b;c;d 测试1;测试2;测试3;测试4
2 e 测试5
4 f;g 测试6;测试7
简单解释一下那个Sql吧：
1、最内层的Sql（即表tmpTable1），按No和Value排序，并列出行号：
select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a
该语句结果如下：
NO VALUE NAME RNFIRST
1 d 测试4 1
1 c 测试3 2
1 b 测试2 3
1 a 测试1 4
2 e 测试5 5
4 g 测试7 6
4 f 测试6 7

2、外层的Sql（即表tmpTable2），根据No分区，取出当前行对应的下一条记录的行号字段：
select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (这里是tmpTable1的SQL) tmpTable1
lead(rnFirst)：取得下一行记录的rnFirst字段
over(partition by No order by rnFirst) 按rnFirst排序，并按No分区，分区就是如果下一行的No字段与当前行的No字段不相等时，不取下一行记录显示
该语句结果如下：
NO VALUE NAME RNFIRST RNNEXT
1 d 测试4 1 2
1 c 测试3 2 3
1 b 测试2 3 4
1 a 测试1 4 NULL
2 e 测试5 5 NULL
4 g 测试7 6 7
4 f 测试6 7 NULL

3、最后就是最外层的sys_connect_by_path函数与start递归了
sys_connect_by_path(Value, ';')
start with rnNext is null
connect by rnNext = prior rnFirst
这个大概意思就是从rnNext为null的那条记录开始，递归查找，
如果前一记录的rnFirst字段等于当前记录的rnNext字段，就把2条记录的Value用分号连接起来，
大家可以先试试下面这个没有Max和Group的Sql：
select No,
sys_connect_by_path(Value, ';') as Value,
sys_connect_by_path(Name, ';') as Name
from (select No,
Value,
Name,
rnFirst,
lead(rnFirst) over(partition by No order by rnFirst) rnNext
from (select a.No,
a.Value,
a.Name,
row_number() over(order by a.No, a.Value desc) rnFirst
from Test a) tmpTable1) tmpTable2
start with rnNext is null
connect by rnNext = prior rnFirst
结果是：
NO VALUE NAME
1 ;a ;测试1
1 ;a;b ;测试1;测试2
1 ;a;b;c ;测试1;测试2;测试3
1 ;a;b;c;d ;测试1;测试2;测试3;测试4
2 ;e ;测试5
4 ;f ;测试6
4 ;f;g ;测试6;测试7
可以看到，每个No的最后一条记录就是我们要的了
所以在sys_connect_by_path外面套一个Max，再加个Group by No，得到的结果就是行转列的结果了
最后再加一个Ltrim，去掉最前面的那个分号