sqlserver 多条记录合并

 id rq                fjname
1   2018-07-10   a.doc
2   2018-07-10   b.doc

id  rq                  fjname
1   2018-07-10   a.doc;b.doc

表中两条记录只有附件名称不同，
现在需要把日期一样的记录合并一下，
附件名称之间用分号隔开

只要sql 不要存储过程

--1

create table T1 (UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into T1 (UserID,UserName,CityName) values (5,'e','上海')
go

CREATE FUNCTION dbo.f_str(@id nvarchar(50))
RETURNS nvarchar(4000)
AS
BEGIN
DECLARE @r nvarchar (4000)
SET @r=''
SELECT @r=@r+','+ UserName FROM T1 WHERE CityName=@id
RETURN STUFF(@r, 1, 1, '')
END
GO
-- 调用函数
SELECt CityName, value = dbo.f_str(CityName) FROM T1 GROUP BY CityName
drop table T1
drop function dbo.f_str

--2

create table T1 (UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into T1 (UserID,UserName,CityName) values (5,'e','上海')
go
create function f_hb (@id nvarchar(50))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str= @str+','+cast(UserName as varchar) from T1 where CityName =@id
set @str=right(@str , len(@str) -1)
return(@str)
End
go

--调用自定义函数得到结果：
select distinct CityName ,dbo.f_hb(CityName) as value from T1
drop table T1
drop function dbo.f_hb

--3

DECLARE @T1 table(UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into @T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into @T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into @T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into @T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into @T1 (UserID,UserName,CityName) values (5,'e','上海')

SELECT B.CityName,UserList FROM (
SELECT CityName,
UserList=stuff((SELECT ','+UserName FROM @T1 WHERE CityName=A.CityName FOR XML PATH('')), 1 , 1 , '')

FROM @T1 A
GROUP BY CityName
) B

--4

create table T1 (UserID int , UserName nvarchar(50),CityName nvarchar(50));
insert into T1 (UserID,UserName,CityName) values (1,'a','上海')
insert into T1 (UserID,UserName,CityName) values (2,'b','北京')
insert into T1 (UserID,UserName,CityName) values (3,'c','上海')
insert into T1 (UserID,UserName,CityName) values (4,'d','北京')
insert into T1 (UserID,UserName,CityName) values (5,'e','上海')

-- 查询处理
SELECT * FROM(SELECT DISTINCT CityName FROM T1) A
OUTER APPLY(
SELECT[value]=STUFF(REPLACE(REPLACE(
(
SELECT UserName FROM T1 N
WHERE N.CityName = A.CityName
FOR XML AUTO
), '', ''), 1, 1, '')
)n

使用stuff+group by实现

--测试数据
select '2018-07-10' as rq,'a.doc' as fjname
into #dt
union all
select '2018-07-10','b.doc'
union all
select '2018-07-11','c.doc'
union all
select '2018-07-12','d.doc'

--实现语句
select rq, [values]=stuff((select ';'+fjname from #dt t where rq=#dt.rq for xml path('')), 1, 1, '') from #dt group by rq

--删除临时表
drop table #dt

stuff+group by，然后将结果insert到其他表

https://www.cnblogs.com/ggll611928/p/6438737.html

合并用STUFF
教学链接 ：https://blog.csdn.net/u011229848/article/details/49930229

使用stuff+group by实现

--测试数据
select '2018-07-10' as rq,'a.doc' as fjname
into #dt
union all
select '2018-07-10','b.doc'
union all
select '2018-07-11','c.doc'
union all
select '2018-07-12','d.doc'

--实现语句
select rq, [values]=stuff((select ';'+fjname from #dt t where rq=#dt.rq for xml path('')), 1, 1, '') from #dt group by rq

--删除临时表
drop table #dt

stuff+group by https://www.cnblogs.com/ggll611928/p/6438737.html

select distinct rq,(select distinct(fjname)+';' from 表名 for xml path('')) as fjname

from 表名 a

select distinct rq,(select distinct(fjname)+';' from 表名 where rq=a.rq for xml path('')) as fjname

from 表名 a