如何将查询结果重新排列到新列中

Question

我的查询当前给出这些结果

+-------+---------+------------+-------+
| Amount| Location| Description|   User|
+-------+---------+------------+-------+
|  100  |      A  |       Fire |   Jim |
+-------+---------+------------+-------+
|   75  |      B  |      Water |   Joe |
+-------+---------+------------+-------+
|   75  |      B  |       Fire |   Joe |
+-------+---------+------------+-------+
|   58  |      A  |       Fire |   Sam |
+-------+---------+------------+-------+
|   72  |      D  |      Earth |   Jim |
+-------+---------+------------+-------+
|  128  |      C  |        Air |   Bob |
+-------+---------+------------+-------+
|  128  |      A  |        Air |   Sam |
+-------+---------+------------+-------+

我想把这些结果重新排列和汇总。按位置和用户分组。

+----+-----+-----+-----+-----+-------+
|    |  Jim|  Joe|  Sam|  Bob|  Total|
+----+-----+-----+-----+-----+-------+
|   A|  100|    0|  186|    0|    286|
+----+-----+-----+-----+-----+-------+
|   B|    0|  150|    0|    0|    150|
+----+-----+-----+-----+-----+-------+
|   C|    0|    0|    0|  128|    128|
+----+-----+-----+-----+-----+-------+
|   D|   72|    0|    0|    0|     72|
+----+-----+-----+-----+-----+-------+
|   E|    0|    0|    0|    0|      0|
+----+-----+-----+-----+-----+-------+

Answer 1

正如o.Jnes所说，这是一个枢轴问题，可以解决。

这边

CREATE TABLE table1 (
`Amount` INTEGER,
`Location` VARCHAR(1),
`Description` VARCHAR(5),
`User` VARCHAR(3)
);
INSERT INTO table1
(`Amount`, `Location`, `Description`, `User`)
VALUES
('100', 'A', 'Fire', 'Jim'),
('75', 'B', 'Water', 'Joe'),
('75', 'B', 'Fire', 'Joe'),
('58', 'A', 'Fire', 'Sam'),
('72', 'D', 'Earth', 'Jim'),
('128', 'C', 'Air', 'Bob'),
('128', 'A', 'Air', 'Sam');

SET @sql = NULL;
SELECT 
GROUP_CONCAT(DISTINCT
CONCAT(
'SUM(CASE `User` WHEN "',
`User`,
'" THEN `Amount` ELSE 0 END) AS `',
`User`, '`'      
)
ORDER BY `Location`
) 
INTO @sql
FROM table1 as p

SET @sql = CONCAT('select `Location`, ',@sql,', SUM(`Amount`) from table1
group by `Location`
ORDER BY `Location`');
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

位置 |山姆 |吉姆 |乔 |鲍勃 |总和("金额") :------- |--: |--: |--: |--: |------------: 一 |186 |100 |  0 |  0 |          286 乙 |  0 |  0 |150 |  0 |          150 C |  0 |  0 |  0 |128 |          128 D |  0 | 72 |  0 |  0 |           72

db<>在这里小提琴

Answer 2

我暂时选择了这个。我需要优化它以使用列中的任何名称，但这目前有效。

select 
column8 as 'Charge Code',
sum(case when column9 = 'name1' then column6 else 0 end) as name1,
sum(case when column9 = 'name2' then column6 else 0 end) as name2,
sum(case when column9 = 'name3' then column6 else 0 end) as name3,
sum(column6) as 'Total'