(转)Sql Server 行转列合并

最新推荐文章于 2024-03-23 12:02:12 发布

weixin_30340745

最新推荐文章于 2024-03-23 12:02:12 发布

阅读量188

点赞数

原文链接：http://www.cnblogs.com/iplay/archive/2012/06/07/2539952.html

版权

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