How to “flatten” multiple rows with same ID (Oracle 11g) by concatenating multiple fields per row

distinctoracleoracle-11g-r2string-aggregation

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).

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 |

Best Answer

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

Related Solutions

How to install Oracle 10g Express and 11g Enterprise on same machine

It appears that you don't need to worry too much.

Install XE first. You don't get to choose the database name (XE) or the port (1521). Next, install 11g to the default app folder. Modify the TNSNAMES.ora files for both databases so they know about each other. Test connections and create database links and you are good to go.

Sql-server – How to connect multiple users/schemas in Oracle 11g

You need to grant the REFERENCES privilege on the reference table to user2. (See GRANT, Table Privileges section. Note that this cannot be granted to a role, must be granted to the user directly.)

Here's a demo:

SQL> create user user1 identified by user1;
User created.
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.

SQL> create user user2 identified by user2;
User created.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.

SQL> create table user1.ref_table (id number primary key);
Table created.
SQL> insert into user1.ref_table values (1);
1 row created.
SQL> insert into user1.ref_table values (2);
1 row created.
SQL> commit;
Commit complete.

SQL> grant references on user1.ref_table to user2;
Grant succeeded.

SQL> connect user2/user2;
Connected.
SQL> create table oth_table (thing number, fk number);
Table created.
SQL> alter table oth_table add(constraint fk1 foreign key (fk)
  2      references user1.ref_table on delete set null);
Table altered.

SQL> insert into oth_table values (42, 1);
1 row created.
SQL> insert into oth_table values (256, 2);
1 row created.
SQL> commit;
Commit complete.

SQL> connect user1/user1;
Connected.
SQL> delete from ref_table where id = 1;
1 row deleted.
SQL> commit;
Commit complete.

SQL> connect user2/user2;
Connected.
SQL> select * from oth_table;

     THING     FK
---------- ----------
    42
       256      2

If you want admin to do all the work, it'll need the CREATE ANY INDEX privilege, and you still need the REFERENCES grant to user2.

Here's how that could work:

SQL> create user user1 identified by user1;
User created.
SQL> create user user2 identified by user2;
User created.
SQL> create user admin identified by admin;
User created.

--- Grants
----------------
SQL> grant create session, create table, unlimited tablespace to user1;
Grant succeeded.
SQL> grant create session, create table, unlimited tablespace to user2;
Grant succeeded.
SQL> grant create session, create any table,
  2        create any index, alter any table to admin;
Grant succeeded.

--- Create reference table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user1.ref_table (id number primary key);
Table created.

--- Do the "references" grant
----------------
SQL> connect / as sysdba
Connected.
SQL> grant references on user1.ref_table to user2;
Grant succeeded.

--- Create the second table as admin
----------------
SQL> connect admin/admin
Connected.
SQL> create table user2.oth_table (foo number, bar number);
Table created.

--- Add the constraint
----------------
SQL> alter table user2.oth_table add(constraint fk
  2      foreign key (bar) references user1.ref_table(id)
  3      on delete set null);
Table altered.

Best Answer

Related Solutions

How to install Oracle 10g Express and 11g Enterprise on same machine

Sql-server – How to connect multiple users/schemas in Oracle 11g

Related Question