Sqlserver 多条记录合并 转载整理

--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
            ), '<N UserName="', ','), '"/>', ''), 1, 1, '')
)n