表A:
UserID UserName
1 张三
2 李四
……表BUserID 机构ID
1 x1
1 x2
2 y1
2 y2
2 y3
……我想要得到的结果是
UserID 机构ID
1 x1,x2
2 y1,y2,y3多谢!
UserID UserName
1 张三
2 李四
……表BUserID 机构ID
1 x1
1 x2
2 y1
2 y2
2 y3
……我想要得到的结果是
UserID 机构ID
1 x1,x2
2 y1,y2,y3多谢!
(@UserId int) returns varchar(100)
as
begin
declare @s varchar(100)
set @s = ''
select @s = @s + 机构ID + ','
from 表B
where UserID = @UserId
set @s = left(@s, len(@s) - 1)
return(@s)
end
go
select UserId, dbo.testfunc(UserId)
from 表B
group by UserId
或者
select a.UserName, dbo.testfunc(b.UserId)
from 表A a join 表B b on a.UserId = b.UserId
group by a.UserName, b.UserId
有表rowtocol,其数据如下:
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 function f_rowtocol(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(b as varchar) from rowtocol where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
调用自定义函数得到结果:
select distinct a ,dbo.f_rowtocol(a) from rowtocol