Sign Up Login


  1. Categories >> Software >> Databases >> SQL PLSQL
  2. Suggest New Category

how can we write a column values horizontal by using sql stmt;
ex:
select name from table_name;(actual output)
a
b
c
d
require output is
a b c d

Question Posted / ravivarma_mib

Answers were Sorted based on User's Feedback



how can we write a column values horizontal by using sql stmt; ex: select name from table_name;(ac..

Answer / pankaj goyal

SQL wm_concat function
Question: I have a table test_test and I need to count the
distinct mark columns and them display all matching values
on one line:

Name Mark
------- ------
ABC 10
DEF 10
GHI 10
JKL 20
MNO 20
PQR 30

The result should be like this, with the count and the rows
groups onto the same line;

mark count names
---- ----- -----------
10 3 ABC,DEF,GHI
20 2 JKL,MNO
30 1 PQR




Answer: By Laurent Schneider: You could write your own
aggregate function or use WM_CONCAT:

select
mark,
count(*),
wm_concat(name)
from
test_test
group by
mark;

Here is another example of using wm_contcat:

select
deptno,
wm_concat(distinct ename)
from
emp
group by
deptno;


DEPTNO WM_CONCAT(DISTINCTENAME)
---------- ----------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

Is This Answer Correct ?    9 Yes 3 No

how can we write a column values horizontal by using sql stmt; ex: select name from table_name;(ac..

Answer / ajit

select Replace ( Wm_concat( a ), ',' ) Actout
from Tablename;

Is This Answer Correct ?    0 Yes 0 No

how can we write a column values horizontal by using sql stmt; ex: select name from table_name;(ac..

Answer / ramaraju

select listagg(cust,' ') within group(order by cust) from t2;

Is This Answer Correct ?    0 Yes 0 No

how can we write a column values horizontal by using sql stmt; ex: select name from table_name;(ac..

Answer / prativa mishra

select xmlagg(xmlelement(g,column_name)).extract('//text()')
from table_name

Is This Answer Correct ?    0 Yes 2 No

how can we write a column values horizontal by using sql stmt; ex: select name from table_name;(ac..

Answer / kalaiselvi

select name from table_name order by name

Is This Answer Correct ?    1 Yes 12 No

Post New Answer

More SQL PLSQL Interview Questions

what is difference between procedure and function, procedure and trigger?

8 Answers   iFlex,

What are types of joins?

0 Answers  

What is the difference between a database and a relational database?

0 Answers  

how to retrieve last tree records from table? select *from emp where rownum > (select count(*)-3 from emp); i am using this query to get last three records from table but its not giving any output, so please tell me what is the error in this query.

16 Answers  

Explain the uses of a database trigger?

0 Answers  

Why is a primary key important?

0 Answers  

What is Difference between StoredProcedure and function?

3 Answers  

Is stored procedure faster than query?

0 Answers  

How to find the count of letter "L" in HELLO

7 Answers   HP,

I Have A Table Like This. Cityno Cityname Mails 1 Bangalore 8km 2 Hsr Layout 20km 3 Mejistic 30km 4 Jayadeva 55km 5 Itpl 80km 6 Hebbal 115km I Have Data Like This I Want O/p Like This Distance No.ofcity 0-50km 3 51-100km 2 101-150km 4 And So On

6 Answers   DELL, HCL, Oracle,

What is the difference between nested table and varray?

0 Answers  

Is sql a case sensitive language?

0 Answers  

For more SQL PLSQL Interview Questions Click Here
Categories