sqlserver 多条记录合并 5C
 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 不要存储过程

0

10个回答

图片说明

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

1

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

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

1
caozhy
caozhy 正解!
4 个月之前 回复

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

0

图片说明

0

图片说明

0
0
weixin_42671477
weixin_42671477 谢谢
4 个月之前 回复

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

0
0
weixin_42671477
weixin_42671477 谢谢
4 个月之前 回复

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

from 表名 a

0

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

from 表名 a

0
Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!