2

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


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 |


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 |

  • Why "Relevant" for 2 is Yes? The data have a Yes, a No and a NULL. – ypercubeᵀᴹ Aug 14 at 10:50
  • @ypercubeᵀᴹ 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). – nutty about natty Aug 14 at 12:37
  • 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 – ypercubeᵀᴹ Aug 14 at 12:54
  • @ypercubeᵀᴹ 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 Aug 14 at 13:10
2
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

  • Looks so simple and straightforward :) – nutty about natty Aug 14 at 12:47
  • 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 Aug 14 at 13:07
  • Bottom line: I'd like to apply a "listagg" on multiple columns (I might need to update my original question and dummy example accordingly). – nutty about natty Aug 14 at 13:08
  • 1
    @nuttyaboutnatty Because in that example, you should not include the "FurtherDetails" column in the SELECT and GROUP BY columns. – Balazs Papp Aug 14 at 13:16
0

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 |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.