how to display table vertically in Oracle?

by vijay
(hyderabad)

i want to print the table (for ex:emp)in vertical manner is it possible or not pls help me..... ?

Comments for how to display table vertically in Oracle?

Average Rating starstarstarstarstar

Click here to add your own comments

Jun 17, 2015
Rating
starstarstarstarstar
Good post
by: Mr. Parker Trantow

What a great blog you have!

Apr 23, 2015
Rating
starstarstarstarstar
This works with all versions of Oracle
by: Anonymous

This example uses the employees table that is created with the sample hr schema and is not dependent on the version of Oracle you're using.


SELECT
job_id,
LTRIM(SYS_CONNECT_BY_PATH(last_name, ','), ',') the_employees
FROM (
SELECT job_id,
last_name,
row_number() over(PARTITION BY job_id ORDER BY job_id) rownbr,
count(*) over(PARTITION BY job_id) rowcnt
FROM employees
)
WHERE rownbr=rowcnt
START WITH rownbr = 1
CONNECT BY PRIOR rownbr = rownbr -1 AND PRIOR job_id = job_id;


If you're using Oracle 11gR2 or later you can use the LISTAGG function as in the following example.


SELECT
job_id,
listagg(last_name,',') WITHIN GROUP (ORDER BY last_name) the_employees
FROM employees
GROUP BY job_id;


For more help with SQL have a look at our SQL tutorials starting at http://www.asktheoracle.net/oracle-sql-tutorial.html

Also see the Oracle SQL Reference at http://docs.oracle.com/database/121/SQLRF/toc.htm

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.