Interested to Buy Any Domain ? << Click Here >> for more details...
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
- 13366 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 the limitation on the block size of pl/sql?
how does a local variable is defined using t-sql? : Transact sql
Is mariadb a nosql database?
what is primary key? : Sql dba
What is scope of pl sql developer in future?
What is varchar sql?
Mention what does plv msg allows you to do?
Can %notfound return null after a fetch?
What is sql mysql pl sql oracle?
How can a pl sql block be executed?
What is rownum and rowid?
Why function is used in sql?
What is not equal in sql?
what does it mean to have quoted_identifier on? What are the implications of having it off? : Sql dba
How many commands are there in sql?
