有关SQL取数据的问题
这样一张表

syxh ssxh ssdm ssrq ssmc
1 1 a11 20120101 手术1
1 2 a22 20120101 手术2
1 3 a33 20120102 手术3
1 4 a44 20120102 手术4
1 5 a55 20120103 手术5
2 1 a11 20120101 手术1
2 2 a66 20120101 手术6
2 3 a77 20120102 手术7
2 4 a44 20120102 手术4


现在怎么才能把ssrq不同的第一个 ssrq ssmc 取出来

要求变为一下这种格式：
syxh ssrq1 ssmc1 ssrq2 ssmc2 ssrq3 ssmc3
1 20120101 手术1 20120102 手术3 20120103 手术5
2 20120101 手术1 20120102 手术7 null null
琢磨一下午了 都没琢磨出来

希望大家指点赐教

[解决办法]

SQL code

if object_id('[tb]') is not null drop table [tb]gocreate table [tb]([syxh] int,[ssxh] int,[ssdm] varchar(3),[ssrq] datetime,[ssmc] varchar(5))insert [tb]select 1,1,'a11','20120101','手术1' union allselect 1,2,'a22','20120101','手术2' union allselect 1,3,'a33','20120102','手术3' union allselect 1,4,'a44','20120102','手术4' union allselect 1,5,'a55','20120103','手术5' union allselect 2,1,'a11','20120101','手术1' union allselect 2,2,'a66','20120101','手术6' union allselect 2,3,'a77','20120102','手术7' union allselect 2,4,'a44','20120102','手术4'goselect * 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) bexec ('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 手术52           2012-01-01 00:00:00.000 手术1   2012-01-02 00:00:00.000 手术7   NULL                    NULL(2 行受影响)**/