oracle数据透视表中的列 [英] columns in oracle pivot

问题描述

示例选择:

select *
from (
    select 1 cnt, 2 sm, '55' name, 12 month, 2011 year, '12 2011' mnth_txt from dual union all
    select 1 cnt, 2 sm, '54' name, 11 month, 2011 year, '11 2011' mnth_txt from dual union all
    select 1 cnt, 2 sm, '55' name, 11 month, 2011 year, '11 2011' mnth_txt from dual union all
    select 1 cnt, 2 sm, '54' name, 12 month, 2011 year, '12 2011' mnth_txt from dual union all
    select 1 cnt, 2 sm, '55' name, 12 month, 2011 year, '12 2011' mnth_txt from dual union all
    select 1 cnt, 2 sm, '56' name, 12 month, 2010 year, '12 2010' mnth_txt from dual  
) pivot (
    sum(cnt) cnt, sum(sm) sm
    for name in
            ( '55' as "Omsk"
            , '54' as "Novosibirsk"
            , '56' as "Orenburg"
            )
)

输出:

|month| year | mnth_txt |Omsk_cnt|Omsk_sm|Novosibirsk_cnt|Novosibirsk_sm|Orenburg_cnt| Orenburg_sm|
| 12  | 2010 |'12 2010' | (null) | (null)| (null)        | (null)       | 1          | 2          |
| 12  | 2011 |'12 2011' | 2      | 4     | 1             | 2            | (null)     | (null)     |
| 11  | 2011 |'11 2011' | 1      | 2     | 1             | 2            | (null)     | (null)     |

是否可以按时间顺序对记录进行排序，排除列月"和年"? 没有列出所有列.

is it possible to sort the records in chronological order, with the excluding columns "month" and "year"? Without listing all the columns.

UPD

需要:

| mnth_txt |Omsk_cnt|Omsk_sm|Novosibirsk_cnt|Novosibirsk_sm|Orenburg_cnt| Orenburg_sm|
|'12 2010' | (null) | (null)| (null)        | (null)       | 1          | 2          |
|'11 2011' | 1      | 2     | 1             | 2            | (null)     | (null)     |
|'12 2011' | 2      | 4     | 1             | 2            | (null)     | (null)     |

类似:

select mnth_txt, pivoted_columns.*

推荐答案

要对记录进行排序，可以将以下内容添加到sql的末尾

To sort the records you can add the following to the end of your sql

ORDER BY TO_DATE('01/'||month||'/'||year,'dd/mm/yyyy') 

注意:枢轴列名称区分大小写，因此需要用引号引起来

Note: The pivot column names are case sensitive so you need to quote them

这是完整的查询，仅选择您需要的列:

here's the full query, selecting only the columns you require:

 SELECT mnth_txt,"Omsk_CNT","Omsk_SM","Novosibirsk_CNT",
        "Novosibirsk_SM","Orenburg_CNT","Orenburg_SM" 
 FROM
 (
 SELECT *
 FROM (     
  SELECT 1 cnt, 2 sm, '55' name, 12 month, 2011 year, '12 2011' mnth_txt FROM DUAL UNION ALL     
  SELECT 1 cnt, 2 sm, '54' name, 11 month, 2011 year, '11 2011' mnth_txt FROM DUAL UNION ALL    
  SELECT 1 cnt, 2 sm, '55' name, 11 month, 2011 year, '11 2011' mnth_txt FROM DUAL UNION ALL     
  SELECT 1 cnt, 2 sm, '54' name, 12 month, 2011 year, '12 2011' mnth_txt FROM DUAL UNION ALL     
  SELECT 1 cnt, 2 sm, '55' name, 12 month, 2011 year, '12 2011' mnth_txt FROM DUAL UNION ALL     
  SELECT 1 cnt, 2 sm, '56' name, 12 month, 2010 year, '12 2010' mnth_txt FROM DUAL   ) 
  PIVOT ( SUM(cnt) cnt, SUM(sm) sm     
          FOR NAME IN             
          ( '55' AS "Omsk",'54' AS "Novosibirsk", '56' AS "Orenburg" ) 
        )
  ORDER BY TO_DATE('01/'||month||'/'||year,'dd/mm/yyyy')         
 )

这篇关于oracle数据透视表中的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！