I have two tables, named supplier and contacts.

The data in the contact table corresponds to a record on the supplier table.

Data of supplier

Data for the contact

Now, I want to make a query that should return the following result

or should return the following result

CodePudding user response：

You can use MySQL GROUP_CONCAT aggregation function to get your first output table. It's own ORDER BY clause will allow you to check the order of concatenation for the rows.

SELECT s.ID,
       s.Name,
       GROUP_CONCAT(c.Contact ORDER BY c.id)
FROM       Supplier s
INNER JOIN Contact c
        ON s.ID = c.supplierId
GROUP BY s.ID,
         s.Name

You can use the window function ROW_NUMBER to assign a rank to each row inside the Contact table by partitioning on the supplier. Then split the contacts into three columns using an IF statement that will check for the three possible values of the ranking. The MAX aggregation function will allow you to remove the nulls.

SELECT s.ID,
       s.Name,
       MAX(IF(c.rn = 1, c.Contact, NULL)) AS Contact1,
       MAX(IF(c.rn = 2, c.Contact, NULL)) AS Contact2,
       MAX(IF(c.rn = 3, c.Contact, NULL)) AS Contact3
FROM       Supplier s
INNER JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY supplierId
                                        ORDER     BY id) AS rn
            FROM Contact ) c
        ON s.ID = c.supplierId
GROUP BY s.ID,
         s.Name;

This second query may not work if you have more than three customers per supplier. In that case you either modify the query to contain the possible maximum amount of suppliers, or you use a prepared statement. If you really need such a solution, leave a comment below.

For a better understanding, you can play with these solutions here. The first solution will work on any MySQL version while the second one will work with MySQL 8.

CodePudding user response：

Query to show the table like you want :

SELECT supplier.ID, supplier.Name, contact.Contact 
FROM supplier 
INNER JOIN contact 
ON supplier.ID = contact.supplierId;