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,
        max(column_id)
          over (partition by table_name) max_col_id,  
        col_list
      from (  
        select ss.table_name, column_name,
            column_id,
            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
BONUS 4 ,ENAME,JOB,SAL,COMM
DEPT 3 ,DEPTNO,DNAME,LOC
EMP 8 ,EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
SALGRADE 3 ,GRADE,LOSAL,HISAL
Advertisements

Blog at WordPress.com.