Oracle 竖列如何横行现实
本帖最后由 NobodyCanHelpMe 于 2012-06-12 23:02:27 编辑 表如下显示：

Name     Level      Month

张三       A          1 

李四       B          2    

王五   C     3

张三       B          2 

李四       B          1   

王五   C          1

张三       B          3 

李四       B          3  

王五   B          2

现在要求如下显示：

姓名    1月份   2月份  3月份

张三      A      B      B

李四      B      B      B

王五      C      B      C

求oracle sql语句应该如何写.
[最优解释]
easy，

WITH t1 AS (SELECT   '张三' Name, 'A' "LEVEL", 1 Month FROM DUAL
            UNION ALL
            SELECT   '李四', 'B', 2 Month FROM DUAL
            UNION ALL
            SELECT   '王五', 'C', 3 Month FROM DUAL
            UNION ALL
            SELECT   '张三', 'B', 2 Month FROM DUAL
            UNION ALL
            SELECT   '李四', 'B', 1 Month FROM DUAL
            UNION ALL
            SELECT   '王五', 'C', 1 Month FROM DUAL
            UNION ALL
            SELECT   '张三', 'B', 3 Month FROM DUAL
            UNION ALL
            SELECT   '李四', 'B', 3 Month FROM DUAL
            UNION ALL
            SELECT   '王五', 'B', 2 Month FROM DUAL) 
 
  SELECT   name,
           wm_concat ("一月份") "一月份",
           wm_concat ("二月份") "二月份",
           wm_concat ("三月份") "三月份"
    FROM   (SELECT   name,
                     DECODE (month, 1, "LEVEL") "一月份",
                     DECODE (month, 2, "LEVEL") "二月份",
                     DECODE (month, 3, "LEVEL") "三月份"
              FROM   t1)
GROUP BY   name

[其他解释]
提供另一种方式

WITH t1 AS (SELECT   '张三' Name, 'A' "LEVEL", 1 Month FROM DUAL
            UNION ALL
            SELECT   '李四', 'B', 2 Month FROM DUAL
            UNION ALL
            SELECT   '王五', 'C', 3 Month FROM DUAL
            UNION ALL
            SELECT   '张三', 'B', 2 Month FROM DUAL
            UNION ALL
            SELECT   '李四', 'B', 1 Month FROM DUAL
            UNION ALL
            SELECT   '王五', 'C', 1 Month FROM DUAL
            UNION ALL
            SELECT   '张三', 'B', 3 Month FROM DUAL
            UNION ALL
            SELECT   '李四', 'B', 3 Month FROM DUAL
            UNION ALL
            SELECT   '王五', 'B', 2 Month FROM DUAL)
SELECT   name,
                     Max(DECODE (month, 1, "LEVEL")) "一月份",
                     Max(DECODE (month, 2, "LEVEL")) "二月份",
                     Max(DECODE (month, 3, "LEVEL")) "三月份"
              FROM   t1 GROUP BY NAME 
 

[其他解释]
LS的满足要求

oracle中DECODE 函数应该算最常用的一种 功能也比较强大 一般作为判断 也有比较
[其他解释]
如果你的表比较大，从效率上考虑，我还是建议你老实写个内部函数统计实现转换会比较好。
[其他解释]

引用:

easy，

SQL code
WITH t1 AS (SELECT '张三' Name, 'A' "LEVEL", 1 Month FROM DUAL
UNION ALL
SELECT '李四', 'B', 2 Month FROM DUAL
UNION ALL
SELECT ……

谢谢了。 我研究一下。 回头给你加分 =.=