CODEWITHSUNDEEP
sqloracle-databasestring-aggregation
C Deepak
C Deepak

Reputation: 1278

SQL Rows concatenation based on single column

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.

Upvotes: 1

Views: 3028

Answers (2)

Lieven Keersmaekers
Lieven Keersmaekers

Reputation: 58431

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;

Upvotes: 8

J0HN
J0HN

Reputation: 26921

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.

Upvotes: 0

Related Questions