做一个自定义函数:
create function fname(@name varchar(10))
returns varchar(1000)
as
begin
declare @r varchar(1000)
set @r=''
select @r=@r+'+'+context from [table] where name=@name
set @r=stuff(@r,1,1,'')
return(@r)
end
goselect name,dbo.fname(name) from [table] group by name
create function fname(@name varchar(10))
returns varchar(1000)
as
begin
declare @r varchar(1000)
set @r=''
select @r=@r+'+'+context from [table] where name=@name
set @r=stuff(@r,1,1,'')
return(@r)
end
goselect name,dbo.fname(name) from [table] group by name
set @s=''
select name,context=@s+ID+context
from 表 group by nam
set @s=stuff(@s,1,1,'')
select @s
group by name
create table tb(name varchar(10),id int,context varchar(100))
Insert into tb
select 'A','1','內容1'
union all select 'A','2','內容2'
union all select 'A','3','內容3'
union all select 'A','4','內容4'
union all select 'B','1','B的內容1'
union all select 'B','2','B的內容2'
union all select 'B','3','B的內容3'
union all select 'B','4','B的內容4'
select * from tb--臨時表
create table tem(id int identity(1,1) ,name varchar(10))
insert into tem(name)
select distinct name from tb order by name
select * from tem--結果表
create table tb1(name varchar(10),context varchar(100))declare @i int,@maxid int
declare @s varchar(100),@name varchar(10)
select @i=1
select @maxid=max(id) from temset nocount on
while @i<=@maxid
begin
set @name=''
set @s=''
select @name=name from tem where id=@i
select @s=@s+[CONTEXT]+'+' from tb where name=@name order by id
insert into tb1 values(@name,left(@s,len(@s)-1))
set @i=@i+1
end--刪除表
drop table tb
drop table tem
drop table tb1--結果
select * from tb1
name context
---------------------------------
A 內容1+內容2+內容3+內容4
B B的內容1+B的內容2+B的內容3+B的內容4
.....--結果表
create table tb1(name varchar(10),context varchar(100))declare @i int,@maxid int
declare @s varchar(100),@name varchar(10)
select @i=1
select @maxid=max(id) from temset nocount on
while @i<=@maxid
begin
set @name=''
set @s=''
select @name=name from tem where id=@i
select @s=@s+cast(id as varchar)+[CONTEXT]+'+' from tb where name=@name order by id
insert into tb1 values(@name,left(@s,len(@s)-1))
set @i=@i+1
end--刪除表
drop table tb
drop table tem
drop table tb1--結果
select * from tb1
name context
---------------------------------
A 1內容1+2內容2+3內容3+4內容4
B 1B的內容1+2B的內容2+3B的內容3+4B的內容4