Here are two snips, should help get you passed this hurdle

😎

Snip 1, using logical operators instead of a case statement, works in SQLite but not in TSQL

select

1 AS Group_ID

,T.id

,T.Dept

,T.Total

,TX.col_key

,(T.id = TX.col_key) * T.Total

from t1 T

cross join

(select distinct id as col_key from t1) as TX;

Results

# |1|id|Dept|Total|col_key|(T.id = TX.col_key) * T.Total

--+-+--+----+-----+-------+-----------------------------

1 |1|1 |A |100 |1 |100

2 |1|1 |A |100 |2 |0

3 |1|1 |A |100 |3 |0

4 |1|1 |A |100 |4 |0

5 |1|2 |B |120 |1 |0

6 |1|2 |B |120 |2 |120

7 |1|2 |B |120 |3 |0

8 |1|2 |B |120 |4 |0

9 |1|3 |C |140 |1 |0

10|1|3 |C |140 |2 |0

11|1|3 |C |140 |3 |140

12|1|3 |C |140 |4 |0

13|1|4 |D |150 |1 |0

14|1|4 |D |150 |2 |0

15|1|4 |D |150 |3 |0

16|1|4 |D |150 |4 |150

Snip 2, cross tab set using pre-set column ordinals as key. Note that the group_id is fixed as is the group by column, in a real set this would be a group key column.

select

1 AS Group_ID

,sum((T.id = 1) * T.Total) AS A

,sum((T.id = 2) * T.Total) AS B

,sum((T.id = 3) * T.Total) AS C

,sum((T.id = 4) * T.Total) AS D

from t1 T

cross join

(select distinct id as col_key from t1) as TX

group by 1;

Results

#|1|sum((T.id = 1) * T.Total)|sum((T.id = 2) * T.Total)|sum((T.id = 3) * T.Total)|sum((T.id = 4) * T.Total)

-+-+-------------------------+-------------------------+-------------------------+-------------------------

1|1|400 |480 |560 |600