SQL Server2005合并字段相同的项的实例应用

 

if object_id('tempdb.dbo.#tb') is not null drop table #tb
go
create table #tb (id int identity(1,1),code varchar(2),name varchar(4),phone int)
insert into #tb
select 'aa','张三',132000000 union all
select 'aa','李四',133000000 union all
select 'bb','王五',122000000 union all
select 'bb','照六',144000000 union all
select 'bb','无七',155000000

select *,n=(select count(*) from #tb where code=t.code and id<=t.id)
into #t
from #tb t

select code,
  name1=max(case when n=1 then name end),
  phone1=max(case when n=1 then phone end),
  name2=max(case when n=2 then name end),  
  phone2=max(case when n=2 then phone end) 
from #t  
group by code 

如：实例（下面的实例是我在公司里做OA系统时候应用的）现在我把他收集起来已是可以供大家参考二是自己代码的经验总结

create proc proc_AllCustom
as
begin
select *,n=(select count(*) from CustState where custId=t.custId and cid<=t.cid)
into #t from CustState t --1
create table #Sates(
kid int,
b1 nvarchar(20),c1 int,a1 nvarchar(20),time1 datetime,
b2 nvarchar(20),c2 int,a2 nvarchar(20),time2 datetime,
b3 nvarchar(20),c3 int,a3 nvarchar(20),time3 datetime,
b4 nvarchar(20),c4 int,a4 nvarchar(20),time4 datetime,
b5 nvarchar(20),c5 int,a5 nvarchar(20),time5 datetime
)
insert into #Sates
 select custId, buitype1=max(case when n=1 then buiType end),
  cstate=max(case when n=1 then cstate end),
  account=max(case when n=1 then  account end),
  addtimes=max(case when n=1 then  addtimes end),
  buitype2=max(case when n=2 then buiType end),
  cstate=max(case when n=2 then cstate end),
  account=max(case when n=2 then  account end),
  addtimes=max(case when n=2 then  addtimes end),
  buitype3=max(case when n=3 then buiType end),
  cstate=max(case when n=3 then cstate end),
  account=max(case when n=3 then  account end),
  addtimes=max(case when n=3 then  addtimes end),
  buitype4=max(case when n=4 then buiType end),
  cstate=max(case when n=4 then cstate end),
  account=max(case when n=4 then  account end),
  addtimes=max(case when n=4 then  addtimes end),
  buitype5=max(case when n=5 then buiType end),
  cstate=max(case when n=5 then cstate end),
  account=max(case when n=5 then  account end),
  addtimes=max(case when n=5 then  addtimes end)
  from #t  group by custId --查询的数据
select Cid,ComName,AddressInfo,b1,c1,a1,time1,b2,c2,a2,time2,
b3,c3,a3,time3,b4,c4,a4,time4,b5,c5,a5,time5
from #Sates,Company where #Sates.kid=Company.Cid order by Company.addtimes
drop table #t
drop table  #Sates
end