求表中每行数的断点数，求教大侠们？

[zongliang2015]

如下表举例：
a1 a2 a3 a4 a5 a6 a7 a8 a9 abreak（断点数）
0 0 0 0 1 1 1 2 2 2
0 0 0 0 1 1 2 1 2 4
0 0 0 0 1 1 2 2 1 3
0 0 0 0 1 1 2 2 2 2
0 0 0 0 1 2 1 1 2 4
0 0 0 0 1 2 1 2 1 ?
0 0 0 0 1 2 1 2 2 ?
0 0 0 0 1 2 3 1 1 ?
举例第一行数据，从左往右看0000变为1，第一个不相同，断点数为1，由111往右遇到2，不相同，断点数为2，则本行断点为2；
第二行数据，从左往右看0000变为1，第一个不相同，断点数为1，由11往右变2，断点数为2，由2往右变1，断点数为3，由1变2，断点数为4；
依次类推。
求其他值得断点数。

[qq_17482963]

select * into #t from(
select 0 a1,0 a2,0 a3,0 a4,1 a5,1 a6,1 a7,2 a8,2 a9 union all
select 0 ,0 ,0 ,0 ,1 ,1 ,2 ,1 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,1 ,2 ,2 ,1   union all
select 0 ,0 ,0 ,0 ,1 ,1 ,2 ,2 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,1 ,1 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,1 ,2 ,1   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,1 ,2 ,2   union all
select 0 ,0 ,0 ,0 ,1 ,2 ,3 ,1 ,1 ) a

;with tb as
(
	select ROW_NUMBER()over(order by getdate())id,* from #t
),
tb1 as
(
	select *,ROW_NUMBER()over(PARTITION by id order by getdate())rn from tb p unpivot(num for name in (a1,a2,a3,a4,a5,a6,a7,a8,a9))t
)
select a.*,b.[abreak] from tb a inner join 
(
select a.id,sum(case when a.num=b.num then 0 else 1 end)[abreak] from tb1 a,tb1 b where a.id=b.id and a.rn+1=b.rn group by a.id
) b on a.id=b.id

drop table #t

id                   a1          a2          a3          a4          a5          a6          a7          a8          a9          abreak
-------------------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
1                    0           0           0           0           1           1           1           2           2           2
2                    0           0           0           0           1           1           2           1           2           4
3                    0           0           0           0           1           1           2           2           1           3
4                    0           0           0           0           1           1           2           2           2           2
5                    0           0           0           0           1           2           1           1           2           4
6                    0           0           0           0           1           2           1           2           1           5
7                    0           0           0           0           1           2           1           2           2           4
8                    0           0           0           0           1           2           3           1           1           4

[Tiger_Zhao]

直接可以算的，用不着行列转换

/* 测试数据
WITH table1(a1,a2,a3,a4,a5,a6,a7,a8,a9)AS(
    SELECT 0,0,0,0,1,1,1,2,2 UNION ALL
    SELECT 0,0,0,0,1,1,2,1,2 UNION ALL
    SELECT 0,0,0,0,1,1,2,2,1 UNION ALL
    SELECT 0,0,0,0,1,1,2,2,2 UNION ALL
    SELECT 0,0,0,0,1,2,1,1,2 UNION ALL
    SELECT 0,0,0,0,1,2,1,2,1 UNION ALL
    SELECT 0,0,0,0,1,2,1,2,2 UNION ALL
    SELECT 0,0,0,0,1,2,3,1,1
)*/
SELECT *,
       SIGN(ABS(a1-a2))+
       SIGN(ABS(a2-a3))+
       SIGN(ABS(a3-a4))+
       SIGN(ABS(a4-a5))+
       SIGN(ABS(a5-a6))+
       SIGN(ABS(a6-a7))+
       SIGN(ABS(a7-a8))+
       SIGN(ABS(a8-a9)) abreak
  FROM table1

         a1          a2          a3          a4          a5          a6          a7          a8          a9      abreak
----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
          0           0           0           0           1           1           1           2           2           2
          0           0           0           0           1           1           2           1           2           4
          0           0           0           0           1           1           2           2           1           3
          0           0           0           0           1           1           2           2           2           2
          0           0           0           0           1           2           1           1           2           4
          0           0           0           0           1           2           1           2           1           5
          0           0           0           0           1           2           1           2           2           4
          0           0           0           0           1           2           3           1           1           4

[zongliang2015]

忘了说了，假设就只有0,1,2三个类型的数据。是否可以有简单的解法。

[Tiger_Zhao]

你要统计的是变化次数，所以和取值范围是没有关系的。