if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([syxh] int,[ssxh] int,[ssdm] varchar(3),[ssrq] datetime,[ssmc] varchar(5))
insert [tb]
select 1,1,'a11','20120101','手术1' union all
select 1,2,'a22','20120101','手术2' union all
select 1,3,'a33','20120102','手术3' union all
select 1,4,'a44','20120102','手术4' union all
select 1,5,'a55','20120103','手术5' union all
select 2,1,'a11','20120101','手术1' union all
select 2,2,'a66','20120101','手术6' union all
select 2,3,'a77','20120102','手术7' union all
select 2,4,'a44','20120102','手术4'
go

select * into #
from tb t 
where not exists(select 1 from tb where syxh=t.syxh and ssrq=t.ssrq and ssxh<t.ssxh)

select * from #

declare @sql varchar(8000)
select @sql=isnull(@sql+',','')
  +'max(case when px='+ltrim(px)+' then ssrq end) as [ssrq'+ltrim(px)+'],'
  +'max(case when px='+ltrim(px)+' then ssmc end) as [ssmc'+ltrim(px)+']'
from
(select distinct px=(select count(1) from # where syxh=a.syxh and ssxh<=a.ssxh) from # a) b

exec ('select syxh,'
  +@sql
  +' from (select *,px=(select count(1) from # where syxh=a.syxh and ssxh<=a.ssxh) from # a) b'
  +' group by syxh'
)
/**
syxh        ssrq1                   ssmc1 ssrq2                   ssmc2 ssrq3                   ssmc3
----------- ----------------------- ----- ----------------------- ----- ----------------------- -----
1           2012-01-01 00:00:00.000 手术1   2012-01-02 00:00:00.000 手术3   2012-01-03 00:00:00.000 手术5
2           2012-01-01 00:00:00.000 手术1   2012-01-02 00:00:00.000 手术7   NULL                    NULL

(2 行受影响)
**/