SQL语句实现合并数据(原创)
有一个表:
DepID StaffName
----------------------
101 AAAaa
101 BBBb
101 CCCccc
202 DDDddd
202 EEE
202 FFF
303 GGGGG
表说明,DepID为部门ID,StaffName为员工姓名,一个部门当然会有0..N多个员工 :)
用SQL语句如何将上面的表变成下面组合起来的方式呢。
DepID StaffNames
-------------------
101 AAAaa;BBBb;CCCccc
202 DDDddd;EEE;FFF
303 GGGGG
先创建以上的测试数据表
if
exists
(
select
name
from
sysobjects
where
name
=
'
DepStaffs
'
and
xtype
=
'
U
'
)
drop
table
DepStaffs
create
table
DepStaffs(
DepID
int
not
null
,
StaffName
varchar
(
30
)
not
null
,
)
go
insert
DepStaffs
values
(
101
,
'
AAAaa
'
)
insert
DepStaffs
values
(
101
,
'
BBBb
'
)
insert
DepStaffs
values
(
101
,
'
CCCccc
'
)
insert
DepStaffs
values
(
202
,
'
DDDddd
'
)
insert
DepStaffs
values
(
202
,
'
EEE
'
)
insert
DepStaffs
values
(
202
,
'
FFF
'
)
insert
DepStaffs
values
(
303
,
'
GGGGG
'
)
创建一个SQLSERVER数据库的用户自定义函数:
CREATE
FUNCTION
[
dbo
]
.
[
FN_GetDepStaffNamesByDepID
]
(
@DepID
int
)
RETURNS
[
nvarchar
]
(
4000
)
AS
BEGIN
DECLARE
@ReturnValue
[
nvarchar
]
(
4000
)
SET
@ReturnValue
=
''
SELECT
@ReturnValue
=
@ReturnValue
+
LTRIM
(
RTRIM
(DepStaffs.StaffName))
+
'
;
'
FROM
DepStaffs
WHERE
DepStaffs.DepID
=
@DepID
SET
@ReturnValue
=
ISNULL
(
@ReturnValue
,
'
;
'
)
RETURN
@ReturnValue
END
自定义函数的使用方法是这样的,注意[dbo]不能省:
SELECT
DepID,
[
dbo
]
.
[
FN_GetDepStaffNamesByDepID
]
(DepID)
As
StaffNames
FROM
DepStaffs
效果图:
![snap041.jpg](https://images.cnblogs.com/cnblogs_com/crazywill/snap041.jpg)
上图显示有重复数据,加上Group By过滤掉就可以了,完整的用法:
SELECT
DepID,
[
dbo
]
.
[
FN_GetDepStaffNamesByDepID
]
(DepID)
As
StaffNames
FROM
DepStaffs
GROUP
BY
DepID
最终效果:
![snap042.jpg](https://images.cnblogs.com/cnblogs_com/crazywill/snap042.jpg)