sqlserver 多条记录合并

Question

 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 不要存储过程

Answer 1

--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