(转)Sql Server 行转列 合并

create table tb(id int, test varchar(10))
insert into tb values(1, 'aa')
insert into tb values(1, 'bb')
insert into tb values(2, 'aaa')
insert into tb values(2, 'bbb')
insert into tb values(2, 'ccc')
go

--1. 创建处理函数
CREATE FUNCTION dbo.f_strUnite(@id int)
RETURNS varchar(8000)
AS
BEGIN
    DECLARE @str varchar(8000)
    SET @str = ''
    SELECT @str = @str + ',' + test FROM tb WHERE id=@id
    RETURN STUFF(@str, 1, 1, '')
END
GO
-- 调用函数
SELECt id, test = dbo.f_strUnite(id) FROM tb GROUP BY id

下面这个函数也可以道理一样

CREATE function [dbo].[f_str](@id int) returns nvarchar(1000)
as
begin
    declare @str nvarchar(1000)
    set @str = ''
    SELECT @str = @str + ',' + test FROM tb WHERE id=@id
    set @str = right(@str , len(@str) - 1)
    return @str
end

PS：http://www.cnblogs.com/love-summer/archive/2012/03/27/2419778.html

转载于:https://www.cnblogs.com/iplay/archive/2012/06/07/2539952.html