使用 CASE WHEN 对列进行透视的 SQL 查询 [英] SQL query to pivot a column using CASE WHEN

问题描述

我有下表:

Bank:
name  val   amount
John   1     2000
Peter  1     1999
Peter  2     1854
John   2     1888

我正在尝试编写一个 SQL 查询来给出以下结果:

I am trying to write an SQL query to give the following result:

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

推荐答案

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

这篇关于使用 CASE WHEN 对列进行透视的 SQL 查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！