SQL语句，取数据库中前五条数据，并且把5条数据转成行的形式显示出来

select torquevalue,createdate from(
select row_number()over(partition by createdate order by (select 1))rn from tb
)t where rn<=5

select createdate,
sum(case when rn=1 then torquevalue else 0 end) as [1],
sum(case when rn=2 then torquevalue else 0 end) as [2],
sum(case when rn=3 then torquevalue else 0 end) as [3],
sum(case when rn=4 then torquevalue else 0 end) as [4],
sum(case when rn=5 then torquevalue else 0 end) as [5]
from(
select torquevalue,createdate,rn from(
select row_number()over(partition by createdate order by (select 1))rn from tb
)t where rn<=5
)t1 group by createdate

;with f as
(
select id=row_number()over(order by getdate()),* from tb
)

select
   distinct b *
from
   f a
cross apply
   (select top 5 * from f where create=a.create order by id)b

;with f as
(
select id=row_number()over(order by getdate()),* from tb
)
, f2 as
(
select
   distinct b *
from
   f a
cross apply
   (select top 5 * from f where createdate=a.createdate order by id)b
)

select
  max(case id when 1 then torquevalue else 0 end) as [1],
  max(case id when 2 then torquevalue else 0 end) as [2],
  max(case id when 3 then torquevalue else 0 end) as [3],
  max(case id when 4 then torquevalue else 0 end) as [4],
  max(case id when 5 then torquevalue else 0 end) as [5]
from
  f2

;with f as
(
select id=row_number()over(order by getdate()),* from tb
)
, f2 as
(
select
   distinct b *
from
   f a
cross apply
   (select top 5 * from f where createdate=a.createdate order by id)b
)

select
  max(case id when 1 then torquevalue else 0.0 end) as [1],
  max(case id when 2 then torquevalue else 0.0 end) as [2],
  max(case id when 3 then torquevalue else 0.0 end) as [3],
  max(case id when 4 then torquevalue else 0.0 end) as [4],
  max(case id when 5 then torquevalue else 0.0 end) as [5]
from
  f2

create table buqingle(x int, y date)

insert into buqingle 
select 151,'2011-09-15' union all
select 152,'2011-09-15' union all
select 153,'2011-09-15' union all
select 154,'2011-09-15' union all
select 155,'2011-09-15' union all
select 156,'2011-09-15' union all
select 157,'2011-09-15' union all
select 181,'2011-09-18' union all
select 182,'2011-09-18' union all
select 183,'2011-09-18' union all
select 184,'2011-09-18' union all
select 185,'2011-09-18' union all
select 201,'2011-09-20' union all
select 202,'2011-09-20' union all
select 203,'2011-09-20' union all
select 204,'2011-09-20' union all
select 205,'2011-09-20'


with t2 as
(
select y,x,rn
from
(select row_number() over(partition by y order by getdate()) rn,x,y 
from buqingle) t
where t.rn<=5)
select y '日期',[1] '第一个值',
[2] '第二个值',[3] '第三个值',
[4] '第四个值',[5] '第五个值'
from t2
pivot(sum(x) for rn IN ([1],[2],[3],[4],[5])) t3


  日期       第一个值      第二个值     第三个值     第四个值      第五个值
---------- ----------- ----------- ----------- ----------- -----------
2011-09-15   151         152         153         154         155
2011-09-18   181         182         183         184         185
2011-09-20   201         202         203         204         205

(3 row(s) affected)