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

 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,去掉最前面的那个分号

展开阅读全文

没有更多推荐了,返回首页