CODEWITHSUNDEEP
sqldatabaseoracle-database
Lc0rE
Lc0rE

Reputation: 2346

Join rows in ORACLE DB

I need to join several datas (of differents users) in single rows. I'll try to explain better: I have a table like this (6 rows)

   |  User   | Name   | Data
=============================
 1 |  user1  | Miky   | data1
-----------------------------
 2 |  user2  | Minny  | data1
-----------------------------
 3 |  user1  | Miky   | data2
-----------------------------
 4 |  user1  | Miky   | data3
-----------------------------
 5 |  user3  | Donald | data1
-----------------------------
 6 |  user3  | Donald | data2
-----------------------------

and what I desire is something like that (3 rows)

   |  User   | Name   | Data
=============================
 1 |  user1  | Miky   | data1
   |         |        | data2
   |         |        | data3
-----------------------------
 2 |  user2  | Minny  | data1
-----------------------------
 3 |  user3  | Donald | data1
   |         |        | data2
-----------------------------

how can I join those rows on a same User using the ORACLE DB? Thanks

Upvotes: 0

Views: 386

Answers (1)

Praveen Prasannan
Praveen Prasannan

Reputation: 7123

You might be looking for LISTAGG

This is just separated by comma.

SELECT  "User", "Name",
RTRIM(LISTAGG("Data" || ', ') WITHIN GROUP(ORDER BY 1),', ') "Data"
FROM  Table1
 GROUP BY "User", "Name";

If you need to separate by line breaks, use the below and bind to a label

SELECT  "User", "Name",
RTRIM(LISTAGG("Data" || '<br/>') WITHIN GROUP(ORDER BY 1),', ') "Data"
FROM  Table1
 GROUP BY "User", "Name";

Fiddle

Upvotes: 1

Related Questions