create table roy_a(
ID int ,fieldB varchar(20))
insert roy_a
select 2, 'A' union all
select 2, 'B' union all
select 3, 'A' union all
select 3, 'B' union all
select 2, 'C'
create function roy_b(@id int)
returns varchar(1000)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+fieldB+'/' from roy_a where [id]=@id
select @sql=left(@sql,len(@sql)-1)
return @sql
end
select id,fieldB=dbo.roy_b(id)from roy_a group by id
id fieldB
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
2 A/B/C
3 A/B
有表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
create function fn_fieldB(
@id int
)
returns
varchar(200)
as
begin
declare
@r varchar(200)
set
@r=''
select
@r=@r+fieldB
from
tablename
where
id=@id
return
@r
end
go
--调用
select
id,
dbo.fn_fieldB(id) as fieldB
from
tablename
group by
id