SQL语句监控数据集中左到右第一个1所在的位置

WITH

/* 测试数据

table1(ID,A,B,C,D,E,F,G,H,I,J)AS(

    SELECT   1,0,1,1,1,1,1,1,1,1,1 UNION ALL

    SELECT   2,0,0,1,1,1,1,1,1,1,1 UNION ALL

    SELECT   3,0,0,0,1,1,1,1,1,1,1 UNION ALL

    SELECT   4,0,0,0,0,0,1,1,1,1,1

),*/

a AS (

    SELECT *

     FROM table1

  UNPIVOT (

           value

           FOR col IN (A,B,C,D,E,F,G,H,I,J)

          ) u

)

    SELECT ID,

           MIN(col) firstCol

      FROM a

     WHERE value = 1

  GROUP BY ID

         ID firstCol

----------- --------

          1 B

          2 C

          3 D

          4 F