SQL result table with Joined row items if the other columns are the same. Help?

Question

Here is my problem:

This is the current result:

    |   Name   |  Alias  |   Role   |
        mark       mak       User
        mark       mak       Admin

This is the wanted result:

    |   Name   |  Alias  |    Role    |
        mark       mak     User, Admin

Can this be possible with SQL(using Oracle and Java EE/JPA/Hibernate right now)?

You might want to have a look at this question: http://stackoverflow.com/questions/1120706/is-there-an-oracle-sql-query-that-aggregates-multiple-rows-into-one-row — mal-wan, Nov 09 '11 at 03:56
possible duplicate of [Oracle: Combine multiple results in a subquery into a single comma-separated value](http://stackoverflow.com/questions/492563/oracle-combine-multiple-results-in-a-subquery-into-a-single-comma-separated-val) — OMG Ponies, Nov 09 '11 at 04:56

score 3 · Accepted Answer · edited Nov 12 '11 at 15:33

3

GROUP_CONCAT is a MySQL function.

Here is the SQL for Oracle:

  SELECT Name, LISTAGG(Role, ',') WITHIN GROUP (ORDER BY Role) AS Roles
    FROM Your_Table
GROUP BY Name;

edited Nov 12 '11 at 15:33

NullUserException

answered Nov 09 '11 at 04:06

Softhinker.com

score 0 · Answer 2 · answered Nov 09 '11 at 04:00

As mentioned on similar questions, this is covered in depth in the following Oracle Base article: http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php

In short you're either after the LISTAGG function or the WM_CONCAT function depending on the version of Oracle you're running.

2 Answers2