--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

解决方案 »

  1.   

    create function udf_getname(@colA varchar(10))
    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
      

  2.   

    create table tb(COLA int, COLB varchar(10))
    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 行)
    */
      

  3.   

    create table tb(COLA varchar(10),COLB nvarchar(30))
    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)