Skip to content

Instantly share code, notes, and snippets.

@xtender
Created December 25, 2012 18:24
Show Gist options
  • Save xtender/4374641 to your computer and use it in GitHub Desktop.
Save xtender/4374641 to your computer and use it in GitHub Desktop.
Simple example for returning columns from column position #M to #N
with t_cols_M_to_N as (
select
x1.n
,x2.*
from
xmltable( 'for $r in /ROWSET/*
return $r'
passing
dbms_xmlgen.getxmltype(
-- you can change query here:
q'[
select *
from all_objects o
where rownum<=10
]')
columns
n for ordinality
,xml_data xmltype path '.'
) x1
,xmltable( '/*/*'
passing x1.xml_data
columns
col_n for ordinality
,x_key varchar2(100) path 'name()'
,x_val varchar2(100) path '.'
) x2
where col_n between 3 /* M */ and 5 /* N */
)
select -- header:
0 n
,max(decode(col_n,3,x_key)) col_3
,max(decode(col_n,4,x_key)) col_4
,max(decode(col_n,5,x_key)) col_5
from t_cols_M_to_N
where n=1
union all
select -- data:
n
,max(col_3)
,max(col_4)
,max(col_5)
from t_cols_M_to_N t
pivot
(
max(x_val) for col_n in (3 as col_3,4 as col_4,5 as col_5)
)
group by n
order by n
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment