MySQL/MariaDB: create a pivot table view

Question

Hi have this MySQL (MariaDB) table:

+----+-------+-------+---------+
| id | name  | value | user_id |
+----+-------+-------+---------+
|  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 |
+----+-------+-------+---------+

I need to get a view like this:

+---------+-----+-----+------------+-------+
| user_id | foo | bar | baz | quux | waldo |
+---------+-----+-----+------------+-------+
|      10 | 40  |  15 | 390 | ENG  |     1 |
|      13 | 20  |  15 |   0 | ENG  |     1 |
|      15 | 5   |   1 | 420 | ENG  |     1 |
|      16 | 5   |  15 |   0 | ENG  |     1 |
+---------+-----+-----+-----+------+-------+

I read the following articles about Pivot Tables in MySQL but I need some help in creating the correct query:

Could you help me please?

Please publish what you have tried and describe what you mean by desired result(if different to that in the question) — P.Salmon, Jul 03 '20 at 08:51
@P.Salmon I added the articles links I read, and the desidered result is exactly what I manually composed in the second table. — Mat, Jul 03 '20 at 08:54
All the articles seem clear if you publish what you tried we can point out where you went wrong. — P.Salmon, Jul 03 '20 at 08:56
Consider handling issues of data display in application code — Strawberry, Jul 03 '20 at 09:47
There are decent coding examples in the Q&As off tag `[pivot-table]`; review them. — Rick James, Jul 03 '20 at 15:40
For MariaDB, see See https://stackoverflow.com/a/56670844/1766831 — Rick James, Nov 01 '21 at 02:27

score 4 · Answer 1 · answered Jul 03 '20 at 09:02

It is a pivot table

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');

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;


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

db<>fiddle here

This works well but will fail if 'name' is not a valid sql column name - for instance if it contains a space. I have a KVP table which contains keys such as 'Voltage Rating (DC)' which cause this method to bork. I am trying to find a way of passing the name in backticks but have not succeeded. The keys are in an external table and I have no control over the keys used. — Gasman, Dec 22 '22 at 12:19
@Gasman change it to `CONCAT('MAX(IF(s.name = "', `name`,'", `value`,"")) AS "',name,'"')` — nbk, Dec 22 '22 at 12:24
Thanks! Ive also found that ```SELECT GROUP_CONCAT(DISTINCT CONCAT('MAX(IF(s.name = "', `name`,'", `value`,"")) AS ', QUOTE(`name`)) ) INTO @sql FROM table1;``` is a solution. — Gasman, Dec 22 '22 at 14:49
Yes that is also a solution, but the po es t is still helpful — nbk, Dec 22 '22 at 15:18

score 1 · Accepted Answer · answered Jul 03 '20 at 09:14

I managed in creating the correct query:

SELECT
  user_id,
  MAX(CASE WHEN name = "foo" THEN value END) "foo",
  MAX(CASE WHEN name = "bar" THEN value END) "bar",
  MAX(CASE WHEN name = "baz" THEN value END) "baz",
  MAX(CASE WHEN name = "quux" THEN value END) "quux",
  MAX(CASE WHEN name = "waldo" THEN value END) "waldo"
FROM table
GROUP BY user_id
ORDER BY user_id ASC

MySQL/MariaDB: create a pivot table view

2 Answers2

Linked