Created
April 20, 2012 14:50
-
-
Save vlfig/2429252 to your computer and use it in GitHub Desktop.
Educational pivot query
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/* Educational pivot query | |
* rank() is oracle-specific, see http://www.adp-gmbh.ch/ora/sql/analytical/rank.html | |
*/ | |
with tab_cols as ( | |
select | |
table_name, | |
column_name, | |
rank() over (partition by table_name order by column_name asc) as ordinal | |
from user_tab_columns | |
) | |
, tab_cols_pivot as ( | |
select | |
table_name, | |
max(case when ordinal = 1 then column_name else null end) as col1, | |
max(case when ordinal = 2 then column_name else null end) as col2, | |
max(case when ordinal = 3 then column_name else null end) as col3, | |
max(case when ordinal = 4 then column_name else null end) as col4, | |
max(case when ordinal = 5 then column_name else null end) as col5, | |
max(case when ordinal = 6 then column_name else null end) as col6, | |
max(case when ordinal = 7 then column_name else null end) as col7, | |
max(case when ordinal = 8 then column_name else null end) as col8, | |
max(case when ordinal = 9 then column_name else null end) as col9 | |
from tab_cols | |
group by table_name | |
) | |
select | |
table_name, col1 || | |
case when col2 is null then null else ', ' || col2 end || | |
case when col3 is null then null else ', ' || col3 end || | |
case when col4 is null then null else ', ' || col4 end || | |
case when col5 is null then null else ', ' || col5 end || | |
case when col6 is null then null else ', ' || col6 end || | |
case when col7 is null then null else ', ' || col7 end || | |
case when col8 is null then null else ', ' || col8 end || | |
case when col9 is null then null else ', ' || col9 end | |
from tab_cols_pivot |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment