Columns in oracle

Question

select select:

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" ) ) 

exit:

 |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 records in chronological order, excluding the columns "month" and "year"? Without listing all columns.

UPD

necessary:

 | 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) | 

sort of:

 select mnth_txt, pivoted_columns.* 

Answer 1

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

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

Note. Pivot table column names are case sensitive, so you need to specify them

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

  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') )