MSSQL列记录合并

创建表及插入数据

If OBJECT_ID(N'Demo') Is Not Null
    Begin
        Drop Table Demo
    End
Else
    Begin
        Create Table Demo(
        Area nvarchar(30),
        Name nvarchar(20))

        Insert Into Demo(Area,Name)
        Values(N'北京',N'张三'),
        (N'上海',N'李四'),
        (N'深圳',N'王五'),
        (N'深圳',N'钱六'),
        (N'北京',N'赵七'),
        (N'北京','Tom'),
        (N'上海','Amy'),
        (N'北京','Joe'),
        (N'深圳','Leo')
    End
Go

SELECT ','+Name FROM dbo.Demo FOR XML PATH('')

Declare @NameCollection nvarchar(500)
Select @NameCollection=ISNULL(@NameCollection+',','')+Name From dbo.Demo
Select @NameCollection as NameCollection

SELECT  Area,
(SELECT ','+Name FROM dbo.Demo WHERE Area = t.Area FOR XML PATH(''))
AS NameCollection FROM dbo.Demo AS t

SELECT Area,
STUFF((SELECT ','+Name FROM dbo.Demo WHERE Area = t.Area FOR XML PATH('')),1,1,'')
AS NameCollection FROM dbo.Demo AS t

 

建一个函数，传入一个Area参数，根据Area来进行合并，返回合并值

CREATE FUNCTION MergeByColumn 
(
    -- Add the parameters for the function here
    @Area nvarchar(30)
)
RETURNS nvarchar(500)
AS
BEGIN
    -- Declare the return variable here
    DECLARE @NC nvarchar(500)  

    -- Add the T-SQL statements to compute the return value here
    SELECT @NC=ISNULL(@NC+',','')+Name FROM dbo.Demo WHERE Area=@Area

    -- Return the result of the function
    RETURN @NC

END
GO

SELECT DISTINCT Area,dbo.MergeByColumn(Area) AS NameCollection From dbo.Demo

SELECT Area,dbo.MergeByColumn(Area) AS NameCollection From dbo.Demo GROUP BY Area