• Jobs
  • About Us
  • professionals
    • Home
    • Jobs
    • Courses and challenges
  • business
    • Home
    • Post vacancy
    • Our process
    • Pricing
    • Assessments
    • Payroll
    • Blog
    • Sales
    • Salary Calculator

0

233
Views
How to SELECT when using a pivot table?

Consider the following SQLFiddle.

DROP TABLE IF EXISTS appointments;
CREATE TABLE appointments (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

DROP TABLE IF EXISTS services;
CREATE TABLE services (
    id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL
);

DROP TABLE IF EXISTS join_services_appointment;
CREATE TABLE join_services_appointment (
    service_id INT(6) UNSIGNED,
    appointment_id INT(6) UNSIGNED,
    FOREIGN KEY(service_id) references services(id),
    FOREIGN KEY(appointment_id) references appointments(id)
);

INSERT INTO appointments (name) VALUES ('appointment1');
INSERT INTO appointments (name) VALUES ('appointment2');
INSERT INTO appointments (name) VALUES ('appointment3');

INSERT INTO services (name) VALUES ('services1');
INSERT INTO services (name) VALUES ('services2');
INSERT INTO services (name) VALUES ('services3');
INSERT INTO services (name) VALUES ('services4');
INSERT INTO services (name) VALUES ('services5');

INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (1, 1);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (2, 1);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (2, 2);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (3, 2);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (3, 3);
INSERT INTO join_services_appointment (service_id, appointment_id) VALUES (4, 3);

What query would I use to get the following results back?

appointment_name   services
appointment1       services1,services2
appointment2       services2,services3
appointment3       services3,services4
about 3 years ago · Santiago Trujillo
1 answers
Answer question

0

As mentioned in the comments, you can use GROUP_CONCAT() to achieve the desired output.

SELECT 
  a.name AS appointment_name,
  GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') AS services
FROM appointments a
INNER JOIN join_services_appointment sa ON a.id = sa.appointment_id
INNER JOIN services s ON s.id = sa.service_id
GROUP BY a.id;
about 3 years ago · Santiago Trujillo Report
Answer question
Find remote jobs

Discover the new way to find a job!

Top jobs
Top job categories
Business
Post vacancy Pricing Our process Sales
Legal
Terms and conditions Privacy policy
© 2025 PeakU Inc. All Rights Reserved.

Andres GPT

Recommend me some offers
I have an error