/*合并法系列*/ --合并法(函数) create table tb1 ( col1 varchar(10), col2 int ) insert into tb1 select 'a',1 union all select 'a',2 union all select 'b',1 union all select 'b',2 union all select 'b',3 create function dbo.FC_Str(@col1 varchar(100)) returns varchar(100) as begin declare @i varchar(100) set @i='' select @i=@i+','+cast(col2 as varchar) from tb1 where col1=@col1 return(stuff(@i,1,1,'')) end select col1,dbo.FC_Str(col1) from tb1 group by col1 --固定行合并法 create table #tb ( col1 varchar(10), col2 int ) insert into #tb select 'a',1 union all select 'a',2 union all select 'b',1 union all select 'b',2 union all select 'c',3 select col1, col2=cast(min(col2) as varchar)+ case when count(*)=1 then '' else +','+cast(max(col2) as varchar) end from #tb group by col1 --临时表合并法 if object_id('tb') is not null drop table tb create table tb ( col1 varchar(10), col2 int ) insert into tb select 'a',1 union all select 'a',2 union all select 'b',1 union all select 'b',2 union all select 'b',3 select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2 declare @col1 varchar(20) declare @col2 varchar(100) update #t1 set @col2= case when @col1=col1 then @col2+ ',' +col2 else col2 end, @col1=col1, col2=@col2 select * from #t1 select col1,col2=max(col2) from #t1 group by col1
http://topic.csdn.net/u/20080919/13/d137f9c5-9589-4344-977c-b6eceecf3bc1.html
--合并法(函数)
create table tb1
(
col1 varchar(10),
col2 int
)
insert into tb1 select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3
create function dbo.FC_Str(@col1 varchar(100))
returns varchar(100)
as
begin
declare @i varchar(100)
set @i=''
select @i=@i+','+cast(col2 as varchar) from tb1 where col1=@col1
return(stuff(@i,1,1,'')) end
select col1,dbo.FC_Str(col1) from tb1 group by col1 --固定行合并法
create table #tb
(
col1 varchar(10),
col2 int
)
insert into #tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'c',3 select col1,
col2=cast(min(col2) as varchar)+
case when count(*)=1 then ''
else +','+cast(max(col2) as varchar) end from #tb
group by col1
--临时表合并法
if object_id('tb') is not null drop table tb
create table tb
(
col1 varchar(10),
col2 int
)
insert into tb select 'a',1
union all select 'a',2
union all select 'b',1
union all select 'b',2
union all select 'b',3 select col1,col2=cast(col2 as varchar(100)) into #t1 from tb order by col1,col2
declare @col1 varchar(20)
declare @col2 varchar(100)
update #t1 set @col2=
case when @col1=col1 then @col2+ ',' +col2
else col2 end,
@col1=col1,
col2=@col2 select * from #t1 select col1,col2=max(col2) from #t1 group by col1
到图书馆找本基础 sql server 看看