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
- 5 Answers
- 11665 Views
- Honeywell, Interact, I also Faced
- E-Mail Answers
Answer Posted / 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 |
Post New Answer View All Answers
What is over () in sql?
What is schema in sql example?
What is clause?
Can I join the same table twice?
What are sql functions? Describe the different types of sql functions?
How do I audit the sql sent to the server?
How can we connect an Android App to an Oracle database and use the PL/SQL procedural code?
how to install mysql? : Sql dba
what are the differences between procedure-oriented languages and object-oriented languages? : Sql dba
How many rows can sqlite handle?
What is the difference between a procedure and a function?
how to use myisamchk to check or repair myisam tables? : Sql dba
How consistent is the view of the data between and within multiple sessions, transactions or statements ?
How many triggers can be applied on a table?
What is data control language?
