By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table1 (
`id` INTEGER,
`name` VARCHAR(5),
`value` VARCHAR(3),
`user_id` INTEGER
);
INSERT INTO table1
(`id`, `name`, `value`, `user_id`)
VALUES
('1', 'foo', '40', '10'),
('2', 'bar', '15', '10'),
('3', 'baz', '390', '10'),
('4', 'quux', 'ENG', '10'),
('5', 'waldo', '1', '10'),
('6', 'foo', '20', '13'),
('7', 'bar', '15', '13'),
('8', 'waldo', '1', '13'),
('9', 'baz', '0', '13'),
('10', 'quux', 'ENG', '13'),
('11', 'baz', '420', '15'),
('12', 'waldo', '1', '15'),
('13', 'bar', '1', '15'),
('14', 'foo', '5', '15'),
('15', 'quux', 'ENG', '15'),
('16', 'waldo', '1', '16'),
('17', 'quux', 'ENG', '16'),
('18', 'foo', '5', '16'),
('19', 'baz', '0', '16'),
('20', 'bar', '15', '16');
Records: 20 Duplicates: 0 Warnings: 0
SET @sql = NULL;
SELECT
GROUP_CONCAT(DISTINCT
CONCAT('MAX(IF(s.name = "', `name`,'", `value`,"")) AS ',name)
) INTO @sql
FROM table1;
SET @sql = CONCAT('SELECT s.user_id, ', @sql, '
FROM table1 s
GROUP BY s.user_id
ORDER BY s.user_id');
SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
| @sql |
|---|
| SELECT s.user_id, MAX(IF(s.name = "bar", `value`,"")) AS bar,MAX(IF(s.name = "baz", `value`,"")) AS baz,MAX(IF(s.name = "foo", `value`,"")) AS foo,MAX(IF(s.name = "quux", `value`,"")) AS quux,MAX(IF(s.name = "waldo", `value`,"")) AS waldo FROM table1 s GROUP BY s.user_id ORDER BY s.user_id |
Statement prepared
| user_id | bar | baz | foo | quux | waldo |
|---|---|---|---|---|---|
| 10 | 15 | 390 | 40 | ENG | 1 |
| 13 | 15 | 0 | 20 | ENG | 1 |
| 15 | 1 | 420 | 5 | ENG | 1 |
| 16 | 15 | 0 | 5 | ENG | 1 |