Query to retrieve one entire column data

Write a query to retrieve one entire column data of multiple rows into one single column of single row?

Questions by shankar

Showing Answers 1 - 14 of 14 Answers

Ali

  • Aug 2nd, 2013
 

Code
  1. SELECT   LTRIM (enamelist,  | ) enamelist

  2.   FROM   (    SELECT   SYS_CONNECT_BY_PATH (ename,  | ) enamelist

  3.                 FROM   (SELECT   ename, ROWNUM rn

  4.                           FROM   (  SELECT   ename

  5.                                       FROM   scott.emp

  6.                                   ORDER BY   ename))

  7.                WHERE   CONNECT_BY_ISLEAF = 1

  8.           START WITH   rn = 1

  9.           CONNECT BY   PRIOR rn = rn - 1)


  Was this answer useful?  Yes

J. Ali (PL/Sql)

  • Nov 29th, 2013
 

Below statements can be used to retrieve one column data (Oracle 11g):

SELECT wm_concat (ename) enamelist
FROM ( SELECT ename
FROM scott.emp
ORDER BY ename);

SELECT listagg (ename, |) WITHIN GROUP (ORDER BY ename) enamelist
FROM ( SELECT ename
FROM scott.emp
ORDER BY ename);

  Was this answer useful?  Yes

Pradipta

  • Feb 7th, 2014
 

SELECT DBMS_LOB.SUBSTR(WM_CONCAT(Ename),3000) FROM Scott.Emp
ORDER BY Ename;

  Was this answer useful?  Yes

RanitB

  • Sep 19th, 2014
 

WM_CONCAT: should not be used in production code as it is not documented by Oracle documentation.

This technique is called as String Aggregation. Use functions like - LISTAGG (>11g), XMLAGG, etc.

Check this - http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php

  Was this answer useful?  Yes

sagar bodala

  • Nov 3rd, 2014
 

by using aggreage functions

  Was this answer useful?  Yes

sudeep

  • Nov 9th, 2014
 

Using GROUP BY

  Was this answer useful?  Yes

hema

  • Nov 12th, 2014
 

Using list aggregate

  Was this answer useful?  Yes

Amar

  • Nov 13th, 2014
 

Here what is the specification of the WM and why are taking for WM

  Was this answer useful?  Yes

Soumya

  • Nov 19th, 2014
 

DECLARE
TYPE tname IS TABLE OF tname.colname%type INDEX BY NUMBER
x tname;
i NUMBER;
CURSOR CURNAME IS SELECT colname FROM tname;
BEGIN
i:=1;
OPEN CURNAME
LOOP
FETCH CURNAME INTO x(i);
i:=i+1;
EXIT WHEN CURNAME%NOTFOUND;
END LOOP;
CLOSE CURNAME;
END;

  Was this answer useful?  Yes

hema nagaraju

  • Dec 20th, 2014
 

SELECT listagg (ename, ,) within group( order by ename) FROM emp

  Was this answer useful?  Yes

Give your answer:

If you think the above answer is not correct, Please select a reason and add your answer below.

 

Related Answered Questions

 

Related Open Questions