set nocount on declare @z varchar(100), @Step int, @q int select @Step = 0, @q = 0declare @ProcessTable table (id int, value varchar(100), ProcessStep int) insert @ProcessTable select id, value, 0 from @YourResultTable order by id, valueupdate @ProcessTable set @z = value = case @q when id then @z else '' end + value + ',' , @q = @q + case @q when id then 0 else 1 end, @Step = ProcessStep = @Step + 1 from @ProcessTableselect id, value from @ProcessTable t join (select max(ProcessStep) MaxStep from @ProcessTable group by id) x on ProcessStep = MaxStepset nocount off
id value ----------- ---------- 1 cool 1 nice 1 wow 2 cool 2 wow 3 cool 3 nice 4 nice This is a result: id value ----------- ------------- 1 cool,nice,wow, 2 cool,wow, 3 cool,nice, 4 nice,实现方法: set nocount on declare @YourResultTable table (id int, value varchar(10)) insert @YourResultTable values(1, 'cool') insert @YourResultTable values(1, 'nice') insert @YourResultTable values(1, 'wow') insert @YourResultTable values(2, 'cool') insert @YourResultTable values(2, 'wow') insert @YourResultTable values(3, 'cool') insert @YourResultTable values(3, 'nice') insert @YourResultTable values(4, 'nice') select * from @YourResultTable declare @z varchar(100), @Step int, @q int select @Step = 0, @q = 0declare @ProcessTable table (id int, value varchar(100), ProcessStep int) insert @ProcessTable select id, value, 0 from @YourResultTable order by id, valueupdate @ProcessTable set @z = value = case @q when id then @z else '' end + value + ',' , @q = @q + case @q when id then 0 else 1 end, @Step = ProcessStep = @Step + 1 from @ProcessTableselect id, value from @ProcessTable t join (select max(ProcessStep) MaxStep from @ProcessTable group by id) x on ProcessStep = MaxStepset nocount off
create function getstr(@ID INT) returns varchar(2000) as begin declare @str varchar(2000) set @str='' select @str=@str+','+内容 from TABLE where content=@content set @str=right(@str,len(@str)-1) return @str end go语句: select distinct 序号 ,dbo.getstr(id) 内容 from TABLE
create function getsql(@con varchar(100)) returns varchar(8000) as begin declare @sql varchar(8000) set @sql='' select @sql=@sql+','+rtrim(内容) from tablename where 序号=@con set @sql=right(@sql,len(@sql)-1) return @sql endselect tablename..getsql(序号) 内容 from tablename group by 序号 drop function getsql
declare @STR varchar(100),@ID INT SELECT * INTO # FROM TABLE order by 序号 ,内容 update # set @STR = 内容= case @ID when 序号 then @STR else '' end + 内容 +',' , @ID = 序号 from #select 序号 ,max(内容) from # group by 序号
create table #a (序号 int, 内容 varchar(100)) insert into #a (序号,内容) values(1,'aaaa') insert into #a (序号,内容) values(1,'bbbb') insert into #a (序号,内容) values(1,'cccc') insert into #a (序号,内容) values(2,'dddd') insert into #a (序号,内容) values(2,'dddd') insert into #a (序号,内容) values(2,'hhhh') declare @Z varchar(100),@id int set @id=1 set @z='' update #a set @z = 内容 = case when @id=序号 then 内容+ ','+ @z else 内容 end ,@id=序号 from #a select 序号,max(内容) from #a group by 序号 drop table #a 序 1 cccc,bbbb,aaaa, 2 hhhh,dddd,dddd
use master go create table t(序号 int, 内容 varchar(20)) insert into t values(1 ,'aaaa') insert into t values(1 ,'bbbb') insert into t values(1 ,'cccc') insert into t values(2 ,'dddd') insert into t values(2 ,'eeee') go create function getstring(@xu int) returns varchar(8000) as begin declare @sql varchar(8000) set @sql='' select @sql=@sql+','+rtrim(内容) from t where 序号=@xu set @sql=right(@sql,len(@sql)-1) return @sql end goselect master.dbo.getstring(序号)as 内容 from t group by 序号go drop table t go drop function getstring
declare @z varchar(100),
@Step int,
@q int
select @Step = 0,
@q = 0declare @ProcessTable table (id int, value varchar(100), ProcessStep int)
insert @ProcessTable
select id, value, 0
from @YourResultTable
order by id, valueupdate @ProcessTable
set @z = value = case @q when id then @z else '' end + value +
',' ,
@q = @q + case @q when id then 0 else 1 end,
@Step = ProcessStep = @Step + 1
from @ProcessTableselect id,
value
from @ProcessTable t
join (select max(ProcessStep) MaxStep
from @ProcessTable
group by id) x
on ProcessStep = MaxStepset nocount off
----------- ----------
1 cool
1 nice
1 wow
2 cool
2 wow
3 cool
3 nice
4 nice
This is a result:
id value
----------- -------------
1 cool,nice,wow,
2 cool,wow,
3 cool,nice,
4 nice,实现方法:
set nocount on
declare @YourResultTable table (id int, value varchar(10))
insert @YourResultTable values(1, 'cool')
insert @YourResultTable values(1, 'nice')
insert @YourResultTable values(1, 'wow')
insert @YourResultTable values(2, 'cool')
insert @YourResultTable values(2, 'wow')
insert @YourResultTable values(3, 'cool')
insert @YourResultTable values(3, 'nice')
insert @YourResultTable values(4, 'nice')
select * from @YourResultTable
declare @z varchar(100),
@Step int,
@q int
select @Step = 0,
@q = 0declare @ProcessTable table (id int, value varchar(100), ProcessStep int)
insert @ProcessTable
select id, value, 0
from @YourResultTable
order by id, valueupdate @ProcessTable
set @z = value = case @q when id then @z else '' end + value +
',' ,
@q = @q + case @q when id then 0 else 1 end,
@Step = ProcessStep = @Step + 1
from @ProcessTableselect id,
value
from @ProcessTable t
join (select max(ProcessStep) MaxStep
from @ProcessTable
group by id) x
on ProcessStep = MaxStepset nocount off
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=''
select @str=@str+','+内容
from TABLE
where content=@content
set @str=right(@str,len(@str)-1)
return @str
end
go语句:
select distinct 序号 ,dbo.getstr(id) 内容
from TABLE
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+rtrim(内容)
from tablename
where 序号=@con
set @sql=right(@sql,len(@sql)-1)
return @sql
endselect tablename..getsql(序号) 内容 from tablename group by 序号
drop function getsql
SELECT * INTO # FROM TABLE order by 序号 ,内容
update #
set @STR = 内容= case @ID when 序号 then @STR else '' end + 内容 +',' , @ID = 序号
from #select 序号 ,max(内容) from # group by 序号
insert into #a (序号,内容) values(1,'aaaa')
insert into #a (序号,内容) values(1,'bbbb')
insert into #a (序号,内容) values(1,'cccc')
insert into #a (序号,内容) values(2,'dddd')
insert into #a (序号,内容) values(2,'dddd')
insert into #a (序号,内容) values(2,'hhhh')
declare @Z varchar(100),@id int
set @id=1
set @z=''
update #a
set @z = 内容 = case when @id=序号 then 内容+ ','+ @z else 内容 end ,@id=序号
from #a
select 序号,max(内容) from #a group by 序号
drop table #a
序
1 cccc,bbbb,aaaa,
2 hhhh,dddd,dddd
go
create table t(序号 int, 内容 varchar(20))
insert into t values(1 ,'aaaa')
insert into t values(1 ,'bbbb')
insert into t values(1 ,'cccc')
insert into t values(2 ,'dddd')
insert into t values(2 ,'eeee')
go
create function getstring(@xu int)
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+rtrim(内容) from t where 序号=@xu
set @sql=right(@sql,len(@sql)-1)
return @sql
end
goselect master.dbo.getstring(序号)as 内容 from t group by 序号go
drop table t
go
drop function getstring
谢谢大家,结贴