Show multiple records in a row

Question

Suppose I've created 2 tables. one is students and another is events. The tables structures and the records are like them. I would like to show the events result in one row separated by commas.

--
-- Table structure for table `events`
--

CREATE TABLE `events` (
  `id` int(11) NOT NULL,
  `event_name` varchar(100) NOT NULL,
  `event_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `events`
--

INSERT INTO `events` (`id`, `event_name`, `event_id`) VALUES
(1, 'dance', 1),
(2, 'sing', 2),
(3, 'acting', 3),
(4, 'debate', 4);

-- --------------------------------------------------------

--
-- Table structure for table `students`
--

CREATE TABLE `students` (
  `id` int(100) NOT NULL,
  `student_name` text NOT NULL,
  `event_id` int(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--
-- Dumping data for table `students`
--

INSERT INTO `students` (`id`, `student_name`, `event_id`) VALUES
(1, 'student 1', 1),
(2, 'student1', 2),
(3, 'student1', 3),
(4, 'student1', 4),
(5, 'student2', 3);
(6, 'student3', 2);
(7, 'student3', 4);

I want to see the results like this

Students  |  Events
----------|---------
Student1  | dance, sing, acting, debate
Student2  | acting
Student3  | sing, debate

What would be the SQL query?

Thanks

I was trying to follow Laravel's migration tables' convention. Not sure if I was correct. — Romi, Dec 25 '17 at 16:07

Tim Biegeleisen · Accepted Answer · 2017-12-25T03:51:01.173

4

Just do a group concatenation along with a join between the two tables:

SELECT
    s.student_name AS Students,
    GROUP_CONCAT(e.event_name) AS Events
FROM students s
LEFT JOIN events e
    ON s.event_id = e.event_id
GROUP BY
    s.student_name;

Demo

Note that your table schema appears to not be completely normalized. Typically, the way to handle this problem is to have a single table for student information, a single table for events, and then a junction table which connects students to events. Your students table appears to be serving both as a junction table and as a table containing unique student information.

So a better way to proceed here would be to refactor students to contain this:

(1, 'student1'),
(2, 'student2'),
(3, 'student3');

And create a new junction table student_event to contain the relationship between students and events:

(id, student_id, event_id)
(1, 1, 1),
(2, 1, 2),
(3, 1, 3),
(4, 1, 4),
(5, 2, 3),
(6, 3, 2),
(7, 3, 4);

edited Dec 25 '17 at 03:51

answered Dec 25 '17 at 03:38

Tim Biegeleisen

502,043
27
286
360

Thanks, I've been using TSQL and found that there is no function like GROUP_CONCAT(). Is there any way to achieve the same result in MSSQL? @Tim Biegeleisen – Romi Dec 25 '17 at 16:05
@Romi [See here](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) for how to simulate group concat in SQL Server. This is a different question than what you asked though. You should open a new question if you need a query for SQL Server, but be sure to include your query attempt in order to maximize the chance of getting help. – Tim Biegeleisen Dec 25 '17 at 16:08
I was trying to get the result, but it's not grouping as it. Please check. http://rextester.com/RXK72524 – Romi Dec 25 '17 at 18:40

Show multiple records in a row

1 Answers1

Demo

Linked