0

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

Lc0rE
  • 2,236
  • 8
  • 26
  • 34
  • 1
    `JOIN` has a special meaning in SQL. So, you just want to `aggregate` some values. But your requested output doesn't tell us what do you want in *Data* "column" - should it be an array? This may be relevant: http://stackoverflow.com/questions/4326868/equivalent-to-postgresql-array-array-to-string-functions-in-oracle-9i – Jan Spurny Aug 01 '13 at 15:37
  • What version of Oracle are you using? Is the maximum length of the concatenated `Data` column in the result going to be less than 4000 bytes? Or does the output need to be a `CLOB`? If you want new lines, what flavor of line breaks do you want between the concatenated values (LF, CR, or CR+LF)? – Justin Cave Aug 01 '13 at 15:39

1 Answers1

1

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

Praveen Prasannan
  • 7,093
  • 10
  • 50
  • 70