我有的是:table
_______________________________________
| MachUUID | State| Pass | Fail |
|--------------------------------------
| 1234 | A | 0.2 | 0.98 |
| 1234 | B | 0.5 | 0.5 |
| 1234 | C | 0.8 | 0.2 |
---------------------------------------
我想要的是:一张table
| MachUUID | A_Pass | A_Fail | B_Pass | B_Fail | C_Pass | C_Fail
--------------------------------------------------------------
| 1234 | 0.2 | 0.98 | 0.5 | 0.5 | 0.8 | 0.2
状态数(a、b等是固定的)。目前他们已经有20个了。所以要改变这个
我在做什么:
Transformed_Table AS (
SELECT MachUUID, Pass AS A_Pass, Fail AS A_Fail
FROM table
WHERE State = 'A'
UNION ALL
SELECT MachUUID, Pass AS B_Pass, Fail AS B_Fail
FROM table
WHERE State = 'B'
UNION ALL
SELECT MachUUID, Pass AS C_Pass, Fail AS C_Fail
FROM table
WHERE State = 'C')
然而,这返回了一个奇怪的结合,看起来像:
我得到了错误的输出
| MachUUID | A_Pass| A_Fail |
| 1234 | 0.2 | 0.98 |
| 1234 | 0.5 | 0.5 |
| 1234 | 0.8 | 0.2 |
问题
我认为在这一点上我对工会的理解是错误的。我了解外饰,不知道这样做是否更好。
我愿意接受关于解决这个问题的其他方法的建议
1条答案
按热度按时间tuwxkamq1#
您可以使用条件聚合来执行此操作: