I have Table with values
X Y Z
- --- -
1 abc P
1 def Q
I need a normal query (not pl/sql) which can give result as
X Y Z
- ------- ---
1 abc,def P,Q
i.e Based on column X the values are converted into csv format
There can be more than three columns.
You can choose any one of the following techniques to aggregate your strings
On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value. Using the SCOTT.EMP table as an example, we might want to retrieve a list of employees for each department. Below is a list of the base data and the type of output we would like to return from an aggregate query.
Base Data: DEPTNO ENAME ---------- ---------- 20 SMITH 30 ALLEN 30 WARD 20 JONES 30 MARTIN 30 BLAKE 10 CLARK 20 SCOTT 10 KING 30 TURNER 20 ADAMS 30 JAMES 20 FORD 10 MILLER Desired Output: DEPTNO EMPLOYEES ---------- -------------------------------------------------- 10 CLARK,KING,MILLER 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,BLAKE,MARTIN,TURNER,JAMES,WARD
Personally, I find this syntax very readable
SELECT x, wm_concat(y), wm_concat(z)
FROM xyz
GROUP BY x;
For Postgre you could use something like
select X, array_to_string(array_accum(Y), ',') as Y, array_to_string(array_accum(Z), ',') as Z
from Table
group by X
It's array_to_string and array_accum are built-in functions.
