By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE people( id INT, name VARCHAR(10), attribute_id INT );
INSERT INTO people VALUES
(1, 'Ken', 1),
(1, 'Ken', 2),
(1, 'Ken', 3),
(2, 'John', 4)
Records: 4 Duplicates: 0 Warnings: 0
CREATE TABLE attributes( id INT, attributes VARCHAR(10) );
INSERT INTO attributes VALUES
(1, 'Tall'),
(2, 'Slender'),
(3, 'Blonde'),
(4, 'Short')
Records: 4 Duplicates: 0 Warnings: 0
SELECT ID, Name,
MAX( CASE WHEN rn = 1 THEN attributes END ) AS attribute1,
MAX( CASE WHEN rn = 2 THEN attributes END ) AS attribute2,
MAX( CASE WHEN rn = 3 THEN attributes END ) AS attribute3
FROM
(
SELECT p.ID, Name, attributes,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rn
FROM people AS p
LEFT JOIN attributes AS a
ON a.ID = p.attribute_ID
) AS pa
GROUP BY ID, Name
| ID | Name | attribute1 | attribute2 | attribute3 |
|---|---|---|---|---|
| 1 | Ken | Tall | Slender | Blonde |
| 2 | John | Short | null | null |
SET @sql = NULL;
SELECT GROUP_CONCAT(
DISTINCT
CONCAT(
'MAX(CASE WHEN rn =', rn,' THEN attributes END) AS attribute',rn
)
)
INTO @sql
FROM
(
SELECT DISTINCT ROW_NUMBER() OVER (PARTITION BY id) AS rn
FROM people
ORDER BY rn
) AS r;
SET @sql = CONCAT('SELECT ID, Name, ',@sql,
' FROM
(
SELECT p.ID, Name, attributes,
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) AS rn
FROM people AS p
LEFT JOIN attributes AS a
ON a.ID = p.attribute_ID
) AS pa
GROUP BY ID, Name');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
Statement prepared
| ID | Name | attribute1 | attribute2 | attribute3 |
|---|---|---|---|---|
| 1 | Ken | Tall | Slender | Blonde |
| 2 | John | Short | null | null |