oracle aide

August 30, 2007

Comma-separated list of columns

Filed under: Analytic functions — oracleaide @ 10:14 pm

This SQL saved me from writing loopy code in a client-side application. I am using this SELECT to populate combo boxes.
Removing the first comma is not a problem.
I have left it on purpose – it adds an empty entry on top of each drop-down list.

alter session set current_schema = scott

select table_name, max_col_id col_count, col_list
  from (
    select table_name, column_id,
          over (partition by table_name) max_col_id,  
      from (  
        select ss.table_name, column_name,
            sys_connect_by_path(cc.column_name, ',') col_list
          from all_tab_columns cc,  
            (select table_name
               from all_tables
               where owner =
               sys_context ('userenv', 'current_schema')) ss
          where cc.table_name = ss.table_name and
            cc.owner = sys_context ('userenv', 'current_schema')
          connect by prior cc.column_id = cc.column_id - 1 and
          prior cc.table_name = cc.table_name and
          prior cc.owner = sys_context ('userenv', 'current_schema')
          start with column_id = 1)
  where column_id = max_col_id

The output is copied directly from Toad:

table_name col_count col_list

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Please log in using one of these methods to post your comment: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

Create a free website or blog at

%d bloggers like this: