求sql算法

[lmm9407]

已知表如下：
ptime sid pvalue
00:00 1 1
00:00 2 2
00:00 3 3
01:01 2 4
01:01 3 5
02:01 1 6
02:01 2 7
03:01 1 8
03:01 2 9
03:01 3 10

现在通过sql语句查询得到如下结果：

ptime 1 2 3
00:00 1 2 3
02:01 6 7
03:01 8 9 10

就是以sid=1的时间为准，列出同一时间其它sid的值，求高手帮忙想一下算法

[昵称被占用了]

select ptime ,max(case when sid = 1 then pvalue end ) as [1] ,max(case when sid = 2 then pvalue end ) as [2] ,max(case when sid = 3 then pvalue end ) as [3] from tab group by ptime having max(case when sid = 1 then pvalue end ) is not null

[骑着蜗牛去爬山]

declare @sql nvarchar(max)
select @sql=isnull(@sql,'')+',max(case when row='+rtrim(number)+' then pvalue else null end)['+rtrim(number)+']' from  master..spt_values where type='p' and number between 1 and
(select max(ct) from(select ptime,ct=count(1) from tb a where exists(select 1 from tb b where b.sid=1 and a.ptime=b.ptime) group by ptime)tt)
exec('select ptime'+@sql+' from (select *,row=row_number()over(partition by ptime order by getdate()) from tb)t group by ptime having(min(sid)=1)')