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
- 13393 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
how can we find the number of rows in a table using mysql? : Sql dba
What are joins in sql?
Can we use the cursor's to create the collection in PL/SQL?
what are ddl statements in mysql? : Sql dba
Which are sql * plus commands?
What are the datatypes available in pl/sql ?
What are sql injection vulnerabilities?
Is sql the best database?
Is sql a backend language?
Is oracel sql developer written in java?
How sql query is executed?
What are all the common sql functions?
How many types of functions are there in sql?
What is the use of sqlerrd 3?
What is posting?
