使用 CASE WHEN 透视列的 SQL 查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5846007/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
提示：将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间：2020-09-01 10:21:30 来源:igfitidea点击:
SQL query to pivot a column using CASE WHEN
提问by Roger
I have the following table:
我有下表：
Bank:
name val amount
John 1 2000
Peter 1 1999
Peter 2 1854
John 2 1888
I am trying to write an SQL query to give the following result:
我正在尝试编写 SQL 查询以提供以下结果：
name amountVal1 amountVal2
John 2000 1888
Peter 1999 1854
So far I have this:
到目前为止，我有这个：
SELECT name,
CASE WHEN val = 1 THEN amount ELSE 0 END AS amountVal1,
CASE WHEN val = 2 THEN amount ELSE 0 END AS amountVal2
FROM bank
However, it gives the slightly wrong result:
但是，它给出了稍微错误的结果：
name amountVal1 amountVal2
John 2000 0
Peter 1999 0
John 0 1888
Peter 0 1854
How can I modify my query to give the correct presentation? Thanks
如何修改我的查询以提供正确的演示文稿？谢谢
回答by nos
SELECT
name,
SUM(CASE WHEN val = 1 THEN amount ELSE 0 END) AS amountVal1,
SUM(CASE WHEN val = 2 THEN amount ELSE 0 END) AS amountVal2
FROM bank GROUP BY name
回答by Brian Willis
Looks like you need to join the table on itself. Try this:
看起来你需要自己加入表格。尝试这个：
select bank1.name, bank1.amount, bank2.amount
from bank bank1
inner join bank bank2 on
bank1.name = bank2.name
and bank1.val = 1
and bank2.val = 2
