Follow Our FB Page << CircleMedia.in >> for Daily Laughter. We Post Funny, Viral, Comedy Videos, Memes, Vines...


ename empno deptno
amar 1 10
akbar 2 20
anthonny 3 30
jonathan 4 40

write a procedure to dispaly the column values ina row
separated by a
deleimiter
eg - input - select ename from emp '|' output -
amar|akbar|anthony|jonathan
input - select empno from emp '@' o/p - 1@2@3@4
input - select deptno from emp '/' o/p -
10/20/30/40

Pls answer this questn.

Answers were Sorted based on User's Feedback



ename empno deptno amar 1 10 akbar 2 20 anthonny ..

Answer / tina

create a function


--concat column values

create or replace function conactfunc (query_1 in varchar2)
return varchar2
is
v_return varchar2(4000);
v_query_concat varchar2(4000);
cur sys_refcursor;
begin
open cur for query_1;

loop
fetch cur into v_query_concat;
EXIT WHEN cur%NOTFOUND;
if v_return is null then
v_return := v_query_concat;
else
v_return := v_return || '@' || v_query_concat;
end if;
end loop;
return v_return;
end;


show_err;

and then call it


select hr.conactfunc('select region_name from regions') from dual;

O/P

Europe@Americas@Asia@Middle East and Africa

Is This Answer Correct ?    3 Yes 0 No

ename empno deptno amar 1 10 akbar 2 20 anthonny ..

Answer / sujatha nulu

Hi,
We have well advanced concepts in Oracle to achieve this.
LISTAGG analytical function can be used for this.

If its ename to be concatenated yet separated by delimiter,
use this way;

SELECT LISTAGG(ename,'|')
WITH IN GROUP (ORDER BY DEPTNO)
FROM EMPLOYEES
GROUP BY DEPTNO
ORDER BY DEPTNO;

You can write a procedure and pass column, delimiter as
parameters.

Is This Answer Correct ?    2 Yes 0 No

Post New Answer

More SQL PLSQL Interview Questions

How do you update a value in sql?

0 Answers  


How many tables can you join in sql?

0 Answers  


how can we transpose a table using sql (changing rows to column or vice-versa) ? : Sql dba

0 Answers  


what is view? : Sql dba

0 Answers  


Can we commit in trigger?

0 Answers  






What is sql used for?

0 Answers  


What is sql in oracle?

0 Answers  


How to remove duplicate rows from a table?.

3 Answers  


Show the two pl/sql cursor exceptions.

0 Answers  


Where do we use pl sql?

0 Answers  


What is the difference between sql/pl-sql/embeded sql?

3 Answers  


What are Global Temporary tables

3 Answers   IBM, TCS,


Categories