关于SQL语句实现合并数据的有关问题
关于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
SQLITE又是怎么实现的那,请大家帮帮忙,在线等!!!
------解决方案--------------------
------解决方案--------------------
没有SQLITE环境,没法帮你写.
比较通用的方法: 用自定义函数,拼接字符串.
------解决方案--------------------
------解决方案--------------------
有一个表:
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
SQLITE又是怎么实现的那,请大家帮帮忙,在线等!!!
数据
合并
sql
sqlite
------解决方案--------------------
create table #tb(DepID int,StaffName varchar(20))
insert into #tb
select 101,'AAAaa'
union all select 101,'BBBb'
union all select 101,'CCCccc'
union all select 202,'DDDddd'
union all select 202,'EEE'
union all select 202,'FFF'
union all select 303,'GGGGG'
select a.DepID,
stuff((select ';'+StaffName from #tb b
where b.DepID=a.DepID
for xml path('')),1,1,'') 'StaffName'
from #tb a
group by a.DepID
/*
DepID StaffName
101 AAAaa;BBBb;CCCccc
202 DDDddd;EEE;FFF
303 GGGGG
*/
------解决方案--------------------
没有SQLITE环境,没法帮你写.
比较通用的方法: 用自定义函数,拼接字符串.
------解决方案--------------------
--SQL
WITH a1 (DepIDm,StaffName) AS
(
select 101,'AAAaa' UNION ALL
select 101,'BBBb' UNION ALL
select 101,'CCCccc' UNION ALL
select 202,'DDDddd' UNION ALL
select 202,'EEE' UNION ALL
select 202,'FFF' UNION ALL
select 303,'GGGGG'
)
SELECT DepIDm,
STUFF((SELECT ','+StaffName FROM a1 WHERE DepIDm=a.DepIDm FOR XML PATH('')),1,1,'') StaffName
FROM a1 a
GROUP BY DepIDm
------解决方案--------------------
-- SQLITE估计只能用自定义的函数, SQL SERVER参考如下:
CREATE FUNCTION fn_merger(@DepID INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @rv NVARCHAR(MAX)
SET @rv = N''
SELECT @rv = @rv + ',' + StaffName FROM dbo.tb WHERE DepID = @DepID
RETURN STUFF(@rv,1,1,'')
END
GO
SELECT
DepID,
dbo.fn_merger(DepID)
FROM tb
GROUP BY DepID