SQL query to pivot a column using CASE WHEN

Question

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:

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

nos · Accepted Answer · 2011-05-01 02:34:59Z

34

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

answered May 1, 2011 at 2:34

nos

231k60 gold badges436 silver badges516 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Roger Over a year ago

That did the trick. I tried using Group by without the SUM and it didn't work. I see now that you could have the same name, with many different values under the columns amountVal1/2. Therefore I assume the group by name wouldn't know what to do with the numbers. In this case, we need to tell it to SUM them! Thanks for the help

Brian Willis · Accepted Answer · 2011-05-01 02:32:20Z

3

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

answered May 1, 2011 at 2:32

Brian Willis

24.1k9 gold badges50 silver badges50 bronze badges

3 Comments

Roger Over a year ago

Thanks I think this would work also, but I don't want to create a temporary table if possible.

ypercubeᵀᴹ Over a year ago

@Roger: This will not create any temporary table. In some cases, it may even be faster than the GROUP BY - CASE approach.

ypercubeᵀᴹ Over a year ago

But this will show different results if there are persons with rows only for val=1 or val=2. Or with more than one row with same value.

Collectives™ on Stack Overflow

SQL query to pivot a column using CASE WHEN

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related