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 are properties of a transaction? : Sql dba

0 Answers  

How do I remove duplicates in two columns?

0 Answers  

What is the difference between local and global temporary table?

0 Answers  

How many types of primary keys are there?

0 Answers  

Why should I use postgresql?

0 Answers  




What sql does db2 use?

0 Answers  

What are the cursor attributes used in PL/SQL ?

4 Answers  

Define implicit and explicit cursors.

0 Answers  

What is pivot query?

0 Answers  

What is cursor in pl sql with examples?

0 Answers  

Why cannot I use bind variables in ddl/scl statements in dynamic sql?

0 Answers  

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.

2 Answers  

For more SQL PLSQL Interview Questions Click Here
Categories