表1:
id name
1 name11
1 name12
1 name13
2 name21
3 name31
3 name32
表2:
id tablename
1 t1
2 t2
3 t3
想得出这样的效果,
id tablename name
1 t1 name11,name12,name13
2 t2 name21
3 t3 name31,name32
谢谢!
id name
1 name11
1 name12
1 name13
2 name21
3 name31
3 name32
表2:
id tablename
1 t1
2 t2
3 t3
想得出这样的效果,
id tablename name
1 t1 name11,name12,name13
2 t2 name21
3 t3 name31,name32
谢谢!
insert into tb
select 1,'name11'
union all select 1,'name12'
union all select 1,'name13'
union all select 2,'name21'
union all select 3,'name31'
union all select 3,'name32'
gocreate table tt(ID int, tablename varchar(10))
insert into tt select 1,'t1 '
union all select 2,'t2'
union all select 3,'t3'
gocreate function dbo.fc_str(@ID int)
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(name as varchar(100)) from tb where ID=@ID
return stuff(@sql,1,1,'')
end
goselect b.ID,a.tablename,dbo.fc_str(a.ID) as name from tt a,tb b where a.id=b.id group by b.ID,a.tablename,a.IDdrop table tb
drop table tt
drop function dbo.fc_str
create table tb(ID int,NAME varchar(10))
insert into tb
select 1,'name11'
union all select 1,'name12'
union all select 1,'name13'
union all select 2,'name21'
union all select 3,'name31'
union all select 3,'name32'
gocreate table tt(ID int, tablename varchar(10))
insert into tt select 1,'t1 '
union all select 2,'t2'
union all select 3,'t3'
gocreate function dbo.fc_str(@ID int)
returns varchar(100)
as
begin
declare @sql varchar(1000)
set @sql=''
select @sql=@sql+','+cast(name as varchar(100)) from tb where ID=@ID
return stuff(@sql,1,1,'')
end
goselect b.ID,a.tablename,dbo.fc_str(b.ID) as name from tt a,tb b where a.id=b.id group by b.ID,a.tablenamedrop table tb
drop table tt
drop function dbo.fc_str