get Nth column of a table – Laurent Schneider

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”

Vit Spinka August 15, 2007 at 15:21

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…

Laurent Schneider Post authorAugust 15, 2007 at 16:19

is it related to getting the nth column? I wrote about unpivot last year here

pivot table

Vit Spinka August 15, 2007 at 17:39

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

Sonia Sabeen February 13, 2010 at 09:22

How to close the cursor opened in above mentioned
sql statement.

Comments are closed.