110,571
社区成员
发帖
与我相关
我的任务
分享
with table1(num, cnt) as
(
select 'A001', 90 union all
select 'A001', 99 union all
select 'A001', 99 union all
select 'A001', 100 union all
select 'A001', 101 union all
select 'A001', 80 union all
select 'A001', 80 union all
select 'A001', 80 union all
select 'A001', 88 union all
select 'A001', 88 union all
select 'A001', 97 union all
select 'A001', 90 union all
select 'A002', 100 union all
select 'A002', 101 union all
select 'A002', 102 union all
select 'A002', 102 union all
select 'A002', 100 union all
select 'A002', 101
)
, table2 as
(
select num, cnt, ROW_NUMBER() over(partition by num order by getdate())-1 rn from table1
)
select
num
,MAX(case when rn%10=0 then cnt end)cnt1
,MAX(case when rn%10=1 then cnt end)cnt2
,MAX(case when rn%10=2 then cnt end)cnt3
,MAX(case when rn%10=3 then cnt end)cnt4
,MAX(case when rn%10=4 then cnt end)cnt5
,MAX(case when rn%10=5 then cnt end)cnt6
,MAX(case when rn%10=6 then cnt end)cnt7
,MAX(case when rn%10=7 then cnt end)cnt8
,MAX(case when rn%10=8 then cnt end)cnt9
,MAX(case when rn%10=9 then cnt end)cnt10
from table2
group by num,rn/10
order by num, rn/10