PIVOTING in SQL - Comparing CASE vs JOIN

I was thinking about pivoting 3 months of data from 2 people so months go into columns using SQL and comparing some methods to achieve that.

These 3 methods have the same output.

| name  | jan  | feb | mar |
|-------|------|-----|-----|
| Peter | 2000 | 200 | 20  |
| John  | 1000 | 100 | 10  |

Run following code in https://sqliteonline.com/ or any other online editor to try.

Setup

DROP TABLE IF exists bank;
CREATE TABLE bank (name varchar ,month int,amount int);

INSERT INTO bank(name,month,amount)
VALUES('Peter', 1, 2000),
	  ('John', 1, 1000),
      ('Peter', 2, 200),
	  ('John', 2, 100),
      ('Peter', 3, 20),
	  ('John', 3, 10)

Method 1: CASE + GROUP BY

SELECT 
  name,
  SUM(CASE WHEN month = 1 THEN amount ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 2 THEN amount ELSE 0 END) as feb,
  SUM(CASE WHEN month = 3 THEN amount ELSE 0 END) as mar
FROM bank GROUP BY name

Method 2: JOIN + filter in AND

select bank1.name, 
	   bank1.amount as jan, 
       bank2.amount as feb, 
       bank3.amount as mar
from bank bank1
inner join bank bank2 on 
    bank1.name = bank2.name 
    AND bank1.month = 1 
    AND bank2.month = 2
INNER JOIN bank bank3 ON
	bank1.name = bank3.name
    AND bank3.month = 3

Method 3: JOIN + filter in WHERE

select bank1.name, 
	   bank1.amount as jan, 
       bank2.amount as feb, 
       bank3.amount as mar
from bank bank1
inner join bank bank2 on 
    bank1.name = bank2.name 
INNER JOIN bank bank3 ON
	bank1.name = bank3.name
WHERE bank1.month = 1 AND bank2.month = 2 and  bank3.month = 3

My observation is method 1 avoids having to specify bank1/2/3.name as in method 2/3 to avoid 42702 column reference "name" is ambiguous. Also, method 1 nicely inserts 0 for months where someone has no entry in the database, while that will cause the person to totally disappear from results for method 2/3 when any month is missing for someone.

All these methods assume we have 1 row per name per month. If there are database errors causing 2 rows of same person, same month , same/different amount, method 1 will hide that error in the groupby aggregation, showing still as many rows in output as number of people, just with one of their values inflated. For method 2/3 however, such an error will show up as an extra row

Adding bad data

insert into bank(name,month,amount)
VALUES('John', 3, 10) 
--VALUES('John', 3, 5)

Method 2/3 results with bad data

| name  | jan  | feb | mar |
|-------|------|-----|-----|
| Peter | 2000 | 200 | 20  |
| John  | 1000 | 100 | 10  |
| John  | 1000 | 100 | 10  |

.

Questions:

  1. Is the pro for method 2/3 described above a form of defensive sql programming? (my knowledge of that ends at inserting DISTINCT everywhere rather than cleaning the data)
  2. Any other pros and cons between the methods?
  3. All these methods require specifying the columns/joins one by one manually. Is there a way to automatically create the columns? (assuming it is performing 1 hot encoding so there isn’t a fixed number like 12 columns for 12 months)