if object_id('tbTest') is not null
drop table tbTest
if object_id('tbTestCountry') is not null
drop table tbTestCountry
if object_id('fnTest') is not null
drop function fnTest
GO
----创建测试数据
create table tbTest(ID int,ParentID int, NameID int)
insert tbTest
select 1, 1, 1 union all
select 2, 1, 2 union all
select 3, 1, 3 union all
select 4, 2, 4 union all
select 5, 2, 5 union all
select 6, 3, 6
create table tbTestCountry(ID int, Name varchar(10))
insert tbTestCountry
select 1, '中国' union all
select 2, '美国' union all
select 3, '日本' union all
select 4, '英国' union all
select 5, '德国' union all
select 6, '法国'
GO
----创建字符串连接函数,在函数中关联国家名称表
create function fnTest(@ParentID int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + b.name from tbTest as a
inner join tbTestCountry as b on a.NameID = b.ID
where ParentID = @ParentID
RETURN stuff(@str,1,1,'')
end
GO
----查询
select ParentID, dbo.fnTest(ParentID) as Names from tbTest group by ParentID
----清除测试环境
drop function fnTest
drop table tbTest,tbTestCountry
----创建测试数据
create table T_A(ID int,ParentID int, NAMEID int)
insert T_A
select 1, 1, 1 union all
select 2, 1, 2 union all
select 3, 1, 3 union all
select 4, 2, 4 union all
select 5, 2, 6 union all
select 6, 3, 5
GO
create table T_B(id int,name varchar(100))
insert into T_B
select 1,'中国' union all
select 2,'美国' union all
select 3,'日本' union all
select 4,'英国' union all
select 5,'德国' union all
select 6,'法国'
go
create function dbo.fn_merge(@ParentID int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + b.name from T_A as a inner join T_B as b on A.nameid=B.id where a.ParentID = @ParentID
RETURN stuff(@str,1,1,'')
end
GO
----查询
select ParentID, dbo.fn_merge(ParentID) as Names from T_A group by ParentID
----清除测试环境
drop function dbo.fn_merge
drop table T_A,T_B
if object_id('tbTest') is not null
drop table tbTest
if object_id('fnTest') is not null
drop function fnTest
GO
----创建测试数据
create table tbTest(ID int,ParentID int, NAME varchar(10))
insert tbTest
select 1, 1, '中国' union all
select 2, 1, '美国' union all
select 3, 1, '日本' union all
select 4, 2, '英国' union all
select 5, 2, '法国' union all
select 6, 3, '德国'
GO
create function fnTest(@ParentID int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + ',' + name from tbTest where ParentID = @ParentID
RETURN stuff(@str,1,1,'')
end
GO
----查询
select ParentID, dbo.fnTest(ParentID) as Names from tbTest group by ParentID