月    部门号 业绩
一月  1     10
一月  2     20
二月  1     30结果为部门号  一月  二月
1       10   30
2       20   null用一个sql句子或者存储过程弄出来,大家帮忙看看!

解决方案 »

  1.   

    select 部门,sum(case when 月=1 then 业绩 else 0 end) 一月
    ,sum(case when 月=2 then 业绩 else 0 end) 二月
    from tb 
    group by 部门
      

  2.   

    你这个一月二月是硬编码啊,再说这个不用sum,也不用group啊,你应该是理解错了
      

  3.   

    select 部门,
    sum(decode(月,'一月',1,0) 一月 
    ,sum(decode(月,'二月',1,0) 二月 
    from tb 
    group by 部门
      

  4.   

    一楼的理解对的,想要行列转换,用SQL语句的话
    就是max+decode+group
    或者sum+decode+groupdecode也可以用case when来代替
      

  5.   


    select bu as 部门,
    sum(decode(yue,'一月',yeji,null)) as 一月,
    sum(decode(yue,'二月',yeji,null)) as 二月
    from table
    group by 部门
      

  6.   

    select deparid "部门号",
           sum(decode(month,'一月',wage,null)) "一月",
           sum(decode(month,'二月',wage,null)) "二月"
    from aa
    group by deparid
      

  7.   

    select 部门,
    sum(decode(月,'一月',业绩,0) 一月 
    ,sum(decode(月,'二月',业绩,0) 二月 
    from tb 
    group by 部门
      

  8.   

    行列转换问题,我就是在CSDN这里学会的,1楼和3楼的都对,我更喜欢用DECODE,所以我支持3楼,呵呵
      

  9.   

    Oracle11新特性——行列转换语句在11g以前,行列转化是一个比较麻烦的事情。对于列转行,需要使用很多的聚集函数加上DECODE语句。11g推出的PIVOT语法可以轻松的解决列转行的问题。先根据USER_OBJECTS建立一张测试表:SQL> CREATE TABLE T AS SELECT * FROM DBA_SEGMENTS;
    表已创建。为了将列转换为行,10g及以前版本需要:
    SQL> SELECT 
      2   OWNER, 
      3   SUM(DECODE(SEGMENT_TYPE, 'TABLE', BYTES)) TABLE_SIZE, 
      4   SUM(DECODE(SEGMENT_TYPE, 'TABLE PARTITION', BYTES)) TABLE_PART_SIZE, 
      5   SUM(DECODE(SEGMENT_TYPE, 'INDEX', BYTES)) INDEX_SIZE, 
      6   SUM(DECODE(SEGMENT_TYPE, 'INDEX PARTITION', BYTES)) INDEX_PART_SIZE
      7  FROM T
      8  GROUP BY OWNER;OWNER                          TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE
    ------------------------------ ---------- --------------- ---------- ---------------
    WKSYS                             2621440                    5177344
    MDSYS                            11993088          131072    6881280          393216
    YANGTK                            1114112                      65536
    TSMSYS                              65536                      65536
    WK_TEST                           5767168                    6553600
    OUTLN                              196608                     262144
    CTXSYS                            2031616                    3407872
    OLAPSYS                           7471104                    8847360
    FLOWS_FILES                         65536                     262144
    SYSTEM                            7077888         2490368    9568256         3276800
    EXFSYS                            1310720                    2490368
    DBSNMP                            1048576                     524288
    ORDSYS                            3670016                    5832704
    SYSMAN                           53608448                   69402624
    XDB                              17170432                    7471104
    FLOWS_030000                     41680896                   54329344
    SYS                             547356672         8716288  165216256         7929856
    WMSYS                             2424832                    3866624
    已选择18行。在11g中可以使用新增的语法PIVOT:
    SQL> SELECT * 
      2  FROM (SELECT OWNER, SEGMENT_TYPE, BYTES FROM T) 
      3   PIVOT (SUM(BYTES) FOR SEGMENT_TYPE IN 
      4    ('TABLE' TABLE_SIZE, 'TABLE PARTITION' TABLE_PART_SIZE, 
      5     'INDEX' INDEX_SIZE, 'INDEX PARTITION' INDEX_PART_SIZE));OWNER                          TABLE_SIZE TABLE_PART_SIZE INDEX_SIZE INDEX_PART_SIZE
    ------------------------------ ---------- --------------- ---------- ---------------
    WKSYS                             2621440                    5177344
    MDSYS                            11993088          131072    6881280          393216
    YANGTK                            1114112                      65536
    TSMSYS                              65536                      65536
    WK_TEST                           5767168                    6553600
    OUTLN                              196608                     262144
    CTXSYS                            2031616                    3407872
    OLAPSYS                           7471104                    8847360
    FLOWS_FILES                         65536                     262144
    SYSTEM                            7077888         2490368    9568256         3276800
    EXFSYS                            1310720                    2490368
    DBSNMP                            1048576                     524288
    ORDSYS                            3670016                    5832704
    SYSMAN                           53608448                   69402624
    XDB                              17170432                    7471104
    FLOWS_030000                     41680896                   54329344
    SYS                             547356672         8716288  165216256         7929856
    WMSYS                             2424832                    3866624
    已选择18行。简单介绍一下语法。PIVOT操作后面是聚集函数,这里需要计算汇总的空间,因此选择了SUM。然后是设置根据SEGMENT_TYPE的不同的值,显示为不同的列。
      

  10.   

    http://topic.csdn.net/u/20090212/13/6c44dcd1-cca1-4774-8635-720412ae20ea.html