Removing duplicate columns in one row while merging corresponding column values

Question

Apologies if this seems simple to some, I am still in the (very) early stages of learning!

Basically I've got a table database that has multiple users (Users_ID), each with a corresponding access name(NAME). The problem is, some Users have multiple access names, meaning when the data is pulled, there is duplicates in the User_ID column.

I need to remove the duplicates in the User column and join their corresponding access names in the NAME column, so it only takes up 1 row and no data is lost.

The current SQL query I'm using is :

select Table1_user_id, Table2.name,
from Table1
inner join  Table2
on Table1.role_id =  Table2.role_id

An example of what this would return:

USER_ID  |  NAME
-------    --------------
Tim        Level_1 Access
John       Level 2 Access
Tim        Level 2 Access
Mark       Level 3 Access
Tim        Level 3 Access

Ideally, I would remove the duplicates for Tim and display as following:

USER_ID  |  NAME
-------    ----------------------------------------------
Tim        Level_1 Access, Level 2 Access, Level 3 Access
John       Level 2 Access
Mark       Level 3 Access

Thanks in advance for any help regarding this and sorry if something similar has been asked before!

Is this Oracle or MySQL? (You've tagged both MySQL and PL/SQL - the latter of which is Oracle related, which is why I ask) — Boneist, Sep 15 '16 at 15:47
Nope, its not MySQL , its PL/SQL . Will that change the answer below? — Frank - NewToThis, Sep 15 '16 at 16:53
Yes. You should investigate LISTAGG - assuming you're on Oracle 11g or higher. — Boneist, Sep 15 '16 at 17:02
Possible duplicate of [SQL Query to concatenate column values from multiple rows in Oracle](http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle) — Matt, Sep 15 '16 at 17:03
btw just to avoid future confusion, PL/SQL is the in-database programming language. If it's just a query, we're talking about SQL, not PL/SQL. (Hover over the tags for usage notes.) — William Robertson, Sep 15 '16 at 17:13

score 1 · Answer 1 · answered Sep 15 '16 at 16:05

1

Use GROUP_CONCAT with SEPARATOR :

SELECT Table1.user_id, GROUP_CONCAT(Table2.name SEPARATOR ',') AS Ename
FROM Table1
INNER JOIN  Table2 ON Table1.role_id =  Table2.role_id
GROUP BY Table1.user_id

answered Sep 15 '16 at 16:05

Shushil Bohara

5,556
2
15
32

You would get the same result without the SEPARATOR clause – kjmerf Sep 15 '16 at 16:26
Oh, really then if I wanna separate values by semi colon then can you tell me how it is done without SEPARATOR ? yes I know comma is default as you know but I always provide answer in such a wide range so learner can also use these in the future without confusion. – Shushil Bohara Sep 15 '16 at 16:38
The expected results have commas :) – kjmerf Sep 15 '16 at 16:39
Thank you for the info! Unfortunately I get the error : ORA-09904: @GROUP_CONCAT: invalid identifier. Any ideas? :D – Frank - NewToThis Sep 15 '16 at 16:57
Ah this is for mysql, the Listagg function works for PL/SQL. Thanks for the help, appreciate it! – Frank - NewToThis Sep 15 '16 at 17:06
This is for MySQL and I think you are executing it in ORACLE and you just have removed MySQL database tag why ?? – Shushil Bohara Sep 15 '16 at 17:08
As much as a noob as I am, I didn't even know the difference between the two and had originally tagged both Mysql and PL/SQL. A person commented above pointed out my errors and so I removed the incorrect tag as I am running on Oracle. – Frank - NewToThis Sep 15 '16 at 17:13

score 0 · Accepted Answer · answered Sep 15 '16 at 17:06

0

select Table1_user_id, LISTAGG(Table2.name,', ') WITHIN GROUP (ORDER BY Table2.name) as Name
from Table1
inner join  Table2
on Table1.role_id =  Table2.role_id

answered Sep 15 '16 at 17:06

Matt

13,833
2
16
28

Removing duplicate columns in one row while merging corresponding column values

2 Answers2