SQL query to return multiple row into a single row and column of the same table

Question

I would an example table as follows:

TableA

ID / Name / Sport

001 / Max / Football

005 / Steven / Badminton

006 / Cindy / Swimming

001 / Max / Tennis

001 / Max / Rugby

005 / Steven / Football

I would like a SQL query statement to return the result as follows:

ID / Name / SportList

001 / Max / Football; Tennis; Rugby

005 / Steven / Badminton; Football

006 / Cindy / Swimming

Sorry, I have to use '/' to differentiate the column. Unable to attached a file due to low reputation.

Thank you.

take a look at GROUP_CONCAT

Raphaël Althaus
– Raphaël Althaus

2014-04-11 07:44:45 +00:00
Commented Apr 11, 2014 at 7:44 — Raphaël Althaus
– Raphaël Althaus, Commented Apr 11, 2014 at 7:44
stackoverflow.com/questions/1817985/…

RRM
– RRM

2014-04-11 08:10:44 +00:00
Commented Apr 11, 2014 at 8:10 — RRM
– RRM, Commented Apr 11, 2014 at 8:10

Raphaël Althaus · Accepted Answer · 2014-04-11 07:46:47Z

2

select 
 Id,
 Name,
 GROUP_CONCAT(Sport  ORDER BY Sport SEPARATOR '; ')
FROM TableA
GROUP BY  Id, Name

see SqlFiddle

answered Apr 11, 2014 at 7:46

Raphaël Althaus

60.6k6 gold badges106 silver badges127 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Community · Accepted Answer · 2017-05-23 10:33:26Z

0

There is no DB agnostic way to achieve this function.

How do I Create a Comma-Separated List using a SQL Query?

for Oracle:

Group_concat MySQL function's equivalent in Oracle

Is there any function in oracle similar to group_concat in mysql?

edited May 23, 2017 at 10:33

CommunityBot

11 silver badge

answered Apr 11, 2014 at 7:58

RRM

2,72135 silver badges59 bronze badges

Collectives™ on Stack Overflow

SQL query to return multiple row into a single row and column of the same table

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related