--1.创建一个合并的函数
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+COLB from 表A where COLA=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go--调用自定义函数得到结果
select distinct COLA,dbo.fmerg(COLA) from 表A
returns varchar(2000)
as
begin declare @str varchar(2000)
set @str=''
select @str=@str+','+ColB from yourtable where colA=@colA
return substring(@str,2,2000)
end
goselect colA,dbo.udf_getname(colA) as ColB from yourtable
insert tb select 4401,'沥青混凝土'
union all select 4401,'水泥混凝土'
union all select 4405,'水泥混凝土'
union all select 4406,'沥青混凝土'
union all select 4406,'碎石混凝土'
union all select 4412,'沥青混凝土'create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=''
select @str=@str+','+COLB from tb where COLA=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
goselect distinct COLA,dbo.fmerg(COLA) from tb/*
COLA
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4401 沥青混凝土,水泥混凝土
4405 水泥混凝土
4406 沥青混凝土,碎石混凝土
4412 沥青混凝土(所影响的行数为 4 行)
*/
insert into tb select
'4401','沥青混凝土' union all select
'4401','水泥混凝土' union all select
'4405','水泥混凝土' union all select
'4406','沥青混凝土' union all select
'4406','碎石混凝土' union all select
'4412','沥青混凝土'
go
create function udf_getname(@colA varchar(10))
returns varchar(2000)
as
begin declare @str varchar(2000)
set @str=''
select @str=@str+','+COLB from tb where COLA=@colA
return substring(@str,2,2000)
end
go
--测试
select distinct COLA,dbo.udf_getname(COLA) as COLB from tbdrop table tb
--结果
COLA COLB
---------- ------------------------
4401 沥青混凝土,水泥混凝土
4405 水泥混凝土
4406 沥青混凝土,碎石混凝土
4412 沥青混凝土(6 row(s) affected)