MySQL/MariaDB: 피벗 테이블 보기 만들기

MySQL/MariaDB: 피벗 테이블 보기 만들기

안녕하세요. MySQL(MariaDB) 테이블이 있습니다.

+----+-------+-------+---------+
| 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 |
+----+-------+-------+---------+

다음과 같은 보기가 필요합니다.

+---------+-----+-----+------------+-------+
| 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 |
+---------+-----+-----+-----+------+-------+

MySQL의 피벗 테이블에 대한 다음 기사를 읽었지만 올바른 쿼리를 만드는 데 도움이 필요합니다.

• 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

저 좀 도와주시겠어요?

피벗 테이블입니다.

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 | 113 | 15 | 0 | 20 | ENG | 115 | 1 | 420 | 5 | ENG | 116 | 15 | 0 | 5 | ENG | 1

db<>여기로 이동

올바른 쿼리를 생성할 수 있었습니다.

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

언급URL : https://stackoverflow.com/questions/62711642/mysql-mariadb-create-a-pivot-table-view