Educational pivot query

gistfile1.sql

/* 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