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:

• https://riptutorial.com/mysql/example/10441/creating-a-pivot-query
• https://en.wikibooks.org/wiki/MySQL/Pivot_table
• https://codingsight.com/pivot-tables-in-mysql/
• https://modern-sql.com/use-case/pivot

Could you help me please?

Answer 1

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

Answer 2

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