(转)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