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

enter image description here

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 |

• ypercubeᵀᴹ: Why "Relevant" for 2 is Yes? The data have a Yes, a No and a NULL.
• nutty about natty: @ypercubeTM See what I wrote above: "Finally, ...": Iff one row has a "yes" for the "Relevant"-column, then it should be flattened to "yes" (and "yes" only).
• ypercubeᵀᴹ: OK. But if you have both (one or more) Yes and a No, you need only the Yes, right? That makes it easier, see the answer by Balsz
• nutty about natty: @ypercubeTM yes it would BUT in the real data I'll want to apply "listagg" to several columns (I'll update my original question and example to reflect that accordingly): created this sqlfiddle.com/#!4/fd02c/5 to reflect that (but it doesn't yet yield the desired result).
• nutty about natty: stackoverflow.com/questions/17639655/…

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

• nutty about natty: Looks so simple and straightforward :)
• nutty about natty: I tried to expand the original table to include another column lik the listagg "Comment" column here sqlfiddle.com/#!4/fd02c/5 but it doesn't yet work. Any idea how to make it fly?
• nutty about natty: Bottom line: I'd like to apply a "listagg" on multiple columns (I might need to update my original question and dummy example accordingly).
• Balazs Papp: @nuttyaboutnatty Because in that example, you should not include the "FurtherDetails" column in the SELECT and GROUP BY columns.
• nutty about natty: sqlfiddle.com/#!4/fd02c/7

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 |