按行统计符合条件的列数

数据库环境：SQL SERVER 2005　　

　　有数据如图1，要求：统计每行中有多少列的值在20以内，有多少列大于20。在原表的基础上

新增2列显示，实现的结果如图2。

　　        

　　如果直接在原表的基础上对列进行统计，会比较麻烦，可以通过列转行生成数据集1，再对1进行

分组统计得到结果集2，然后将原表和数据集2左联接，即可实现要求。

　　0.数据准备

WITH    x0
          AS ( SELECT   1 AS id ,
                        2 AS one ,
                        8 AS two ,
                        13 AS three ,
                        15 AS four
               UNION ALL
               SELECT   2 AS id ,
                        9 AS one ,
                        11 AS two ,
                        25 AS three ,
                        36 AS four
               UNION ALL
               SELECT   3 AS id ,
                        3 AS one ,
                        4 AS two ,
                        5 AS three ,
                        9 AS four
               UNION ALL
               SELECT   4 AS id ,
                        8 AS one ,
                        13 AS two ,
                        40 AS three ,
                        42 AS four
               UNION ALL
               SELECT   5 AS id ,
                        1 AS one ,
                        3 AS two ,
                        9 AS three ,
                        21 AS four
             )

　　1.列转行

,x1
          AS ( SELECT   id ,
                        attr ,
                        value
               FROM     x0 UNPIVOT( value FOR attr IN ( one, two, three, four ) ) AS t
             )

　　2.分组统计，左联

SELECT  x0.id ,
            one ,
            two ,
            three ,
            four ,
            count1 ,
            count2
    FROM    x0
            LEFT JOIN ( SELECT  id ,
                                SUM(CASE WHEN value <= 20 THEN 1
                                         ELSE 0
                                    END) AS count1 ,
                                SUM(CASE WHEN value > 20 THEN 1
                                         ELSE 0
                                    END) AS count2
                        FROM    x1
                        GROUP BY id
                      ) x2 ON x2.id = x0.id

(本文完)