合并字符串

[wfcfan]

select * from view_tematt

结果：
aid vvalue
4 1
5 生产
6 充电器
6 耳机
6 存储卡

最后结果：
aid vvalue
4 1
5 生产
6 充电器,耳机,存储卡


请问sql语句应该怎么写呀

[青锋-SS]

使用函数

[青锋-SS]

create function f_1(@aid int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+vvalue+',' from view_tematt where aid=@aid
set @sql=left(@sql,len(@sql)-1)
return @sql
end
go
select aid,f_1(aid) as vvalue from view_attemp group by aid

[wfcfan]

什么函数

[中国风]

合并分拆表_整理贴
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

[hery2002]

合并分拆表_整理贴1
http://topic.csdn.net/u/20080612/22/c850499f-bce3-4877-82d5-af2357857872.html

合并表:

SQL2000用函数:

go
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@aidint)
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+vvalue from view_tematt  where aid=@aid
    return @S
end
go
Select distinct aid,vvalue =dbo.F_Str(aid) from view_tematt 

go

[青锋-SS]

create function f_1(@aid int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+vvalue+',' from view_tematt where aid=@aid
set @sql=left(@sql,len(@sql)-1)
return @sql
end
go
create table view_tematt(aid    int,vvalue varchar(20));
go
insert into view_tematt values(4, '1')
insert into view_tematt values(5, '生产')
insert into view_tematt values(6, '充电器')
insert into view_tematt values(6, '耳机')
insert into view_tematt values(6, '存储卡' )
go
select aid,dbo.f_1(aid) as vvalue from view_tematt group by aid

--结果
aid         vvalue
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
4           1
5           生产
6           充电器,耳机,存储卡

(3 行受影响)

[flairsky]

楼上都很详细了，不加解释了

[lqmgf222]

create function f_1(@aid int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+vvalue+',' from view_tematt where aid=@aid
set @sql=left(@sql,len(@sql)-1)
return @sql
end
go
create table view_tematt(aid    int,vvalue varchar(20));
go
insert into view_tematt values(4, '1')
insert into view_tematt values(5, '生产')
insert into view_tematt values(6, '充电器')
insert into view_tematt values(6, '耳机')
insert into view_tematt values(6, '存储卡' )
go
select aid,dbo.f_1(aid) as vvalue from view_tematt group by aid

[lqmgf222]

create function f_1(@aid int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+vvalue+',' from view_tematt where aid=@aid
set @sql=left(@sql,len(@sql)-1)
return @sql
end
go
create table view_tematt(aid    int,vvalue varchar(20));
go
insert into view_tematt values(4, '1')
insert into view_tematt values(5, '生产')
insert into view_tematt values(6, '充电器')
insert into view_tematt values(6, '耳机')
insert into view_tematt values(6, '存储卡' )
go
select aid,dbo.f_1(aid) as vvalue from view_tematt group by aidcreate function f_1(@aid int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+vvalue+',' from view_tematt where aid=@aid
set @sql=left(@sql,len(@sql)-1)
return @sql
end
go
create table view_tematt(aid    int,vvalue varchar(20));
go
insert into view_tematt values(4, '1')
insert into view_tematt values(5, '生产')
insert into view_tematt values(6, '充电器')
insert into view_tematt values(6, '耳机')
insert into view_tematt values(6, '存储卡' )
go
select aid,dbo.f_1(aid) as vvalue from view_tematt group by aid