get Nth column of a table

I answered this question twice, once on otn forums and once on developpez.net

Here is the latest to get the third column of emp

select
column_name as name,
extractvalue(column_value,’/ROW/’||column_name) as value
from table(xmlsequence(cursor(select * from emp))),
user_tab_columns
where COLUMN_ID=3 and table_name=’EMP’
;


NAME VALUE
---- ----------
JOB CLERK
JOB SALESMAN
JOB SALESMAN
JOB MANAGER
JOB SALESMAN
JOB MANAGER
JOB MANAGER
JOB ANALYST
JOB PRESIDENT
JOB SALESMAN
JOB CLERK
JOB CLERK
JOB ANALYST
JOB CLERK

probably useless, but fun 😉

4 thoughts on “get Nth column of a table

  1. Vit Spinka

    And the ultimate solution – select value of the SAL column:
    select empno, col, val
    from emp
    unpivot (val for col in (sal, comm))
    where col=’SAL’;

    (I had to choose sal and comm columns, as the unpivot clause needs identical datatype in processed columns).

    Oh yes, 11g…

  2. Vit Spinka

    Yes, it for example solves the classical problem: get month n from table with columns month01 to month12. The only difference is you don’t specify the column by position, but by name. (Which is definitely safer.)

Comments are closed.