求多个列的最大值/最小值

数据库环境：SQL SERVER 2008R2　

　　如题，现有数据如图1，要求求出每行相同数据类型的最大值/最小值。即图2的效果。

　　Oracle里有专门的greatest()、least()函数求多个列的最大、最小值，但是，在Sql Server里，

还没有对应实现的函数。我想到的方法是通过想列转行、行转列实现。

1.数据准备

WITH    x0          AS ( SELECT   1 AS id ,                        3 AS c1 ,                        4 AS c2 ,                        0 AS c3 ,                        5 AS c4 ,                        2 AS c5               UNION ALL               SELECT   2 AS id ,                        2 AS c1 ,                        3 AS c2 ,                        1 AS c3 ,                        6 AS c4 ,                        4 AS c5               UNION ALL               SELECT   3 AS id ,                        6 AS c1 ,                        4 AS c2 ,                        11 AS c3 ,                        2 AS c4 ,                        9 AS c5             )

2.列转行

, x1          AS ( SELECT   *               FROM     x0 UNPIVOT( c FOR attr IN ( c1, c2, c3, c4, c5 ) ) t             )

3.union all合并每行的最大、最小值

,x2          AS ( SELECT   id ,                        attr ,                        c               FROM     x1               UNION ALL               SELECT   id ,                        'c6' AS attr ,                        MAX(c)               FROM     x1               GROUP BY id               UNION ALL               SELECT   id ,                        'c7' AS attr ,                        MIN(c)               FROM     x1               GROUP BY id             )

4.行转列实现最终结果

 SELECT  id ,            c1 ,            c2 ,            c3 ,            c4 ,            c5 ,            c6 AS c_max ,            c7 AS c_min    FROM    ( SELECT    *              FROM      x2            ) AS t1 PIVOT( MAX(c) FOR attr IN ( c1, c2, c3, c4, c5, c6, c7 ) ) t2

　　SQL脚本是合在一起执行的，这里为了说明思路，把SQL拆开讲了。

当然，实现该功能的方法不止这一种，具体可以参看这篇文章 http://blog.csdn.net/wufeng4552/article/details/4681510/。