數據庫環境：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/。