--带符号合并行列转换--有表t,其数据如下: a b 1 1 1 2 1 3 2 1 2 2 3 1 --如何转换成如下结果: a b 1 1,2,3 2 1,2 3 1 create table tb ( a int, b int ) insert into tb(a,b) values(1,1) insert into tb(a,b) values(1,2) insert into tb(a,b) values(1,3) insert into tb(a,b) values(2,1) insert into tb(a,b) values(2,2) insert into tb(a,b) values(3,1) goif object_id('pubs..f_hb') is not null drop function f_hb go--创建一个合并的函数 create function f_hb(@a int) returns varchar(8000) as begin declare @str varchar(8000) set @str = '' select @str = @str + ',' + cast(b as varchar) from tb where a = @a set @str = right(@str , len(@str) - 1) return(@str) End go--调用自定义函数得到结果: select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果 a b ----------- ------ 1 1,2,3 2 1,2 3 1(所影响的行数为 3 行)
a b
1 1
1 2
1 3
2 1
2 2
3 1
--如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1 create table tb
(
a int,
b int
)
insert into tb(a,b) values(1,1)
insert into tb(a,b) values(1,2)
insert into tb(a,b) values(1,3)
insert into tb(a,b) values(2,1)
insert into tb(a,b) values(2,2)
insert into tb(a,b) values(3,1)
goif object_id('pubs..f_hb') is not null
drop function f_hb
go--创建一个合并的函数
create function f_hb(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from tb where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go--调用自定义函数得到结果:
select distinct a ,dbo.f_hb(a) as b from tbdrop table tb--结果
a b
----------- ------
1 1,2,3
2 1,2
3 1(所影响的行数为 3 行)