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
Answers were Sorted based on User's Feedback
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 |
Answer / ajit
select Replace ( Wm_concat( a ), ',' ) Actout
from Tablename;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / ramaraju
select listagg(cust,' ') within group(order by cust) from t2;
Is This Answer Correct ? | 0 Yes | 0 No |
Answer / prativa mishra
select xmlagg(xmlelement(g,column_name)).extract('//text()')
from table_name
Is This Answer Correct ? | 0 Yes | 2 No |
how instead of triger is helpful for mutating tables errors handlling ?
Which join is like inner join?
what is sql.
What is nvl?
Why do we use procedures?
What is cursor and its types?
What is the difference between nvl function, ifnull function, and isnull function?
what is sql server agent? : Sql dba
Is it possible to access the current value in a session before accessing next value?
can we call a procedure from a function?
How do you update a table in sql?
What is data abstraction in sql?