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. 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. 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.)

Leave a Reply

Your email address will not be published.


*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>