add remove split language chart show hidden hide
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
7 rows affected
id 2023_01 2023_02 2023_03 2023_04 2023_05 2023_06 2023_07 2023_08 2023_09 2023_10 2023_11 2023_12 2024_01 2024_02 2024_03 2024_04 2024_05 2024_06 2024_07 2024_08 2024_09 2024_10 2024_11 2024_12 2025_01
bob 100 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
bob 0 75 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
bob 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
bob 0 0 0 20 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
bob 0 0 0 0 60 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
jennifer 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
jennifer 0 10 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 
SELECT id, [2023_01] = COALESCE([2023_01],0),[2023_02] = COALESCE([2023_02],0),[2023_03] = COALESCE([2023_03],0),[2023_04] = COALESCE([2023_04],0),[2023_05] = COALESCE([2023_05],0),[2023_06] = COALESCE([2023_06],0),[2023_07] = COALESCE([2023_07],0),[2023_08] = COALESCE([2023_08],0),[2023_09] = COALESCE([2023_09],0),[2023_10] = COALESCE([2023_10],0),[2023_11] = COALESCE([2023_11],0),[2023_12] = COALESCE([2023_12],0),[2024_01] = COALESCE([2024_01],0),[2024_02] = COALESCE([2024_02],0),[2024_03] = COALESCE([2024_03],0),[2024_04] = COALESCE([2024_04],0),[2024_05] = COALESCE([2024_05],0),[2024_06] = COALESCE([2024_06],0),[2024_07] = COALESCE([2024_07],0),[2024_08] = COALESCE([2024_08],0),[2024_09] = COALESCE([2024_09],0),[2024_10] = COALESCE([2024_10],0),[2024_11] = COALESCE([2024_11],0),[2024_12] = COALESCE([2024_12],0),[2025_01] = COALESCE([2025_01],0)
  FROM #test
  PIVOT (SUM(val) FOR yr_mo IN ([2023_01],[2023_02],[2023_03],[2023_04],[2023_05],[2023_06],[2023_07],[2023_08],[2023_09],[2023_10],[2023_11],[2023_12],[2024_01],[2024_02],[2024_03],[2024_04],[2024_05],[2024_06],[2024_07],[2024_08],[2024_09],[2024_10],[2024_11],[2024_12],[2025_01])) AS pvt;