insert into MenuPilot(menuname,itemparent) values('Items','parent')
insert into MenuPilot(menuname,itemparent) values('Works','parent')
insert into MenuPilot(menuname,itemparent) values('Tables','parent')
insert into MenuPilot(menuname,itemparent) values('Views','parent')
select * into test from MenuPilot where itemparent = 'parent'
go
--创建一个合并的函数
create function f_hb(@itemparent varchar(10))
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + cast(menuname as varchar) from test where itemparent = @itemparent
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
--调用自定义函数得到结果:
select distinct itemparent ,dbo.f_hb(itemparent) as 显示 from test
有表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