How to "flatten" multiple rows with same ID (Oracle 11g) by concatenating **multiple** fields per row?

Question

I'm connected to an Oracle Database (11g Release 2 - 11.2.0.4).

I'd like to "flatten"/"merge" all rows with the same ID, pretty much as is well delineated here: https://www.w3resource.com/mysql/aggregate-functions-and-grouping/aggregate-functions-and-grouping-group_concat.php

However, in this example the "concatenation" is applied only to one field/column; I'd want to apply it to several fields/columns.

Also, I'd only want to concatenate unique/distinct entries: In the above example that would be

  P002   | CA003

instead of the duplication

 P002   | CA003,CA003

Finally, for the "Relevant"-column, I'd like to only have "Yes" in there, iff at least one row with a given ID has a "Yes" (so more than "just" concatenation required here... probably something different, e.g. "if-then"-equivalent). In other words, it's enough if 1 person thinks it's relevant to make it relevant (in the flattened table).

---

Related links:

Eliminate duplicates in ListAgg (Oracle)

https://livesql.oracle.com/apex/livesql/file/content_HT1O85E4BHSBWN93G1B3M8SI2.html

https://stackoverflow.com/questions/11510870/listagg-in-oracle-to-return-distinct-values

https://stackoverflow.com/questions/17639655/2-listagg-in-one-sql-select-in-oracle

https://modern-sql.com/feature/listagg

---

• SQLFiddle (for the "input"): http://www.sqlfiddle.com/#!4/fd02c/11

And the raw data:

| ID | Relevant | LongDescription |      A |      B |      C |                           Comment |                         FurtherDetails |
|----|----------|-----------------|--------|--------|--------|-----------------------------------|----------------------------------------|
|  1 |      Yes |   text for ID 1 |    Yes | (null) |    Yes |    This is a Tony’s comment for 1 |    Further details on this 1 from Tony |
|  2 |       No |   text for ID 2 | (null) |    Yes | (null) |    This is Andrew’s comment for 2 |  Further details on this 2 from Andrew |
|  2 |      Yes |   text for ID 2 | (null) | (null) | (null) |      This is Mary’s comment for 2 |    Further details on this 2 from Mary |
|  3 |      Yes |   text for ID 3 | (null) | (null) | (null) |                            (null) |                                 (null) |
|  4 |   (null) |   text for ID 4 | (null) | (null) | (null) |    This is George’s comment for 4 |  Further details on this 4 from George |
|  2 |   (null) |   text for ID 2 |    Yes |    Yes | (null) |                            (null) |                                 (null) |
|  7 |       No |   text for ID 7 | (null) | (null) | (null) |                            (null) |                                 (null) |
|  1 |       No |   text for ID 1 | (null) | (null) | (null) | This is a Tiffany’s comment for 1 | Further details on this 1 from Tiffany |
|  1 |      Yes |   text for ID 1 | (null) |    Yes | (null) |                            (null) |     Further details on this 1 from Sam |

---

• SQLFiddle (for the desired "output"): http://www.sqlfiddle.com/#!4/fd02c/10

And the raw data:

| ID | Relevant | LongDescription |      A |      B |      C |                                                             Comment |                                                                                                                  FD | RowCount |
|----|----------|-----------------|--------|--------|--------|---------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------|----------|
|  1 |      Yes |   text for ID 1 |    Yes |    Yes |    Yes | This is a Tiffany’s comment for 1 // This is a Tony’s comment for 1 | Further details on this 1 from Sam // Further details on this 1 from Tiffany // Further details on this 1 from Tony |        3 |
|  2 |      Yes |   text for ID 2 |    Yes |    Yes | (null) |      This is Andrew’s comment for 2 // This is Mary’s comment for 2 |                                        Further details on this 2 from Andrew // Further details on this 2 from Mary |        3 |
|  3 |      Yes |   text for ID 3 | (null) | (null) | (null) |                                                              (null) |                                                                                                              (null) |        1 |
|  4 |   (null) |   text for ID 4 | (null) | (null) | (null) |                                      This is George’s comment for 4 |                                                                               Further details on this 4 from George |        1 |
|  7 |       No |   text for ID 7 | (null) | (null) | (null) |                                                              (null) |                                                                                                              (null) |        1 |

Answer 1

select "ID", max("Relevant") as "Relevant", "LongDescription", 
max("A") as "A",
max("B") as "B",
max("C") as "C",
listagg("Comment", ' // ') within group (order by null) as "Comment"
from dummyTable
group by "ID", "LongDescription"
order by "ID";

http://www.sqlfiddle.com/#!4/af6db4/15

Answer 2

This is based on Balazs Papp's answer and help:

select "ID", max("Relevant") as "Relevant", "LongDescription", 
max("A") as "A",
max("B") as "B",
max("C") as "C",
listagg("Comment", ' // ') within group (order by null) as "Comment",
listagg("FurtherDetails", ' // ') within group (order by null) as "FD",
count(*) as "RowCount"
from dummyTable2
group by "ID", "LongDescription"
order by "ID";

SQLFiddle (for the desired "output"): http://www.sqlfiddle.com/#!4/fd02c/10

And the raw data:

| ID | Relevant | LongDescription |      A |      B |      C |                                                             Comment |                                                                                                                  FD | RowCount |
|----|----------|-----------------|--------|--------|--------|---------------------------------------------------------------------|---------------------------------------------------------------------------------------------------------------------|----------|
|  1 |      Yes |   text for ID 1 |    Yes |    Yes |    Yes | This is a Tiffany’s comment for 1 // This is a Tony’s comment for 1 | Further details on this 1 from Sam // Further details on this 1 from Tiffany // Further details on this 1 from Tony |        3 |
|  2 |      Yes |   text for ID 2 |    Yes |    Yes | (null) |      This is Andrew’s comment for 2 // This is Mary’s comment for 2 |                                        Further details on this 2 from Andrew // Further details on this 2 from Mary |        3 |
|  3 |      Yes |   text for ID 3 | (null) | (null) | (null) |                                                              (null) |                                                                                                              (null) |        1 |
|  4 |   (null) |   text for ID 4 | (null) | (null) | (null) |                                      This is George’s comment for 4 |                                                                               Further details on this 4 from George |        1 |
|  7 |       No |   text for ID 7 | (null) | (null) | (null) |                                                              (null) |                                                                                                              (null) |        1 |