日期:2014-05-17 浏览次数:20536 次
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
*/
--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 + ',' + S