表2：YZJC，为人员获奖情况
目前用的SQL语句
Select JBXX.bh,JBXX.xm,YZJC.jclb from JBXX join YZJC on JBXX.bh=YZJC.bh Order by JBXX.bh asc
得到的结果为：
我现在想得到的结果是：
将同一个人的jclb合并一行显示，请问各位大神SQL语句该怎么写？
2 个解决方案
#1
stuff连接，可以自己查查资料学习学习
select bh,xm,jclb=stuff((select ' '+jclb+';' from yzjc a where a.bh=b.bh for xml path('')),1,1,'')
from jbxx b
group by b.bh
#2
with jbxx(bh,xm) as (
select 1,'张一' union all
select 2,'张二' union all
select 3,'张三' union all
select 4,'张四' union all
select 5,'张五'),yzjc(bh,jclb) as
(select 1,'表扬' union all
select 1,'记功' union all
select 2,'优秀' union all
select 3,'优秀' union all
select 3,'表扬' union all
select 4,'优秀' union all
select 5,'表扬')
select a.bh,a.xm,stuff((select ','+b.jclb from yzjc as b
where a.bh=b.bh for xml path('')),1,1,'') as jclb
from jbxx as a
/*
bh xm jclb
1 张一 表扬,记功
2 张二 优秀
3 张三 优秀,表扬
4 张四 优秀
5 张五 表扬
*/
#1
stuff连接，可以自己查查资料学习学习
select bh,xm,jclb=stuff((select ' '+jclb+';' from yzjc a where a.bh=b.bh for xml path('')),1,1,'')
from jbxx b
group by b.bh
#2
with jbxx(bh,xm) as (
select 1,'张一' union all
select 2,'张二' union all
select 3,'张三' union all
select 4,'张四' union all
select 5,'张五'),yzjc(bh,jclb) as
(select 1,'表扬' union all
select 1,'记功' union all
select 2,'优秀' union all
select 3,'优秀' union all
select 3,'表扬' union all
select 4,'优秀' union all
select 5,'表扬')
select a.bh,a.xm,stuff((select ','+b.jclb from yzjc as b
where a.bh=b.bh for xml path('')),1,1,'') as jclb
from jbxx as a
/*
bh xm jclb
1 张一 表扬,记功
2 张二 优秀
3 张三 优秀,表扬
4 张四 优秀
5 张五 表扬
*/