求助sql script
id value
1 a
2 a
3 b
4 a
5 a
6 a
7 a
8 b
9 a
10 a
value这一列里只会出现a或者b
要求变成如下格式
c1 c2 c3 c4 c5 c6 c7
a1-2 b3-1 a4-4 b8-1 a9-2 null null
解释:
从id 1开始有2个连续不断的a, 所以c1为 a1-2
从id 3开始有1个连续不断的b, 所以c2为 b3-1
从id 4开始有4个连续不断的a, 所以c3为 b4-4
....
以此类推。求指导。
只有30分了，分少见谅。
[解决办法]
with a1 (id,[value]) as
(
select 1 ,'a' union all
select 2 ,'a' union all
select 3 ,'b' union all
select 4 ,'a' union all
select 5 ,'a' union all
select 6 ,'a' union all
select 7 ,'a' union all
select 8 ,'b' union all
select 9 ,'a' union all
select 10 ,'a'
)
,a2 as
(
select *,id-row_number() over(partition by [value] order by id) re
from a1
)
,a3 as
(
select distinct b.id,rtrim(a.[value])+rtrim(b.id)+'-'+rtrim(b.n) [value]
from a2 a
cross apply
(
select min(id) id,count(*) n from a2 where [value]=a.[value] and re=a.re
) b
)
,a4 as
(
select [value],'c'+rtrim(row_number() over(order by id)) colName
from a3
)
select *
from a4
pivot (max([value]) for colName in ([c1],[c2],[c3],[c4],[c5],[c6],[c7])) pvt
