- /*合并法系列*/
- --合并法(函数)
- 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
合并表法
最新推荐文章于 2021-01-26 04:39:52 发布