To select columns as rows from a table

Database
Enthusiast

To select columns as rows from a table

I have 3 rows in a table employee with column empid.
empid
100
200
300

when i give

select emp from employee;

i get as below
100
200
300

Whereas i want it as 100,200,300 in a single row.

Please help me.
2 REPLIES
Enthusiast

Re: To select columns as rows from a table

Hi ,
You can achieve this by following query.
But if columns are increased then the query will be lenghty.
If you send the exact requirement and table structure query can be changed.
I have done in following way:

create volatile table emp (
empid integer )
on commit preserve rows ;

insert into emp values( 100);
insert into emp values( 200);
insert into emp values( 300);

sel trim(max(case when a.rownum = 1 then a.empid END )) || ' , ' ||
trim(max(case when a.rownum = 2 then a.empid END ) ) || ' , ' ||
trim(max(case when a.rownum = 3 then a.empid END ) )
from (
sel empid ,rank(empid asc) rownum
from emp ) a ;

output - 100 , 200 , 300

hope this will work for you.
Junior Supporter

Re: To select columns as rows from a table

Use a recursive query.

There is an example here:

http://carlosal.wordpress.com/2008/10/15/queries-recursivas-en-teradata/

HTH.

Cheers.

Carlos.