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 ' )
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
(
@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
FROM DepStaffs
效果图:
上图显示有重复数据,加上Group By过滤掉就可以了,完整的用法:
SELECT
DepID,
[
dbo
]
.
[
FN_GetDepStaffNamesByDepID
]
(DepID)
As
StaffNames
FROM DepStaffs
GROUP BY DepID
FROM DepStaffs
GROUP BY DepID
最终效果: