add batch remove batch split batch comment selection show hidden batches hide batch highlight batch
db<>fiddle
donate feedback about
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
DROP TABLE IF EXISTS #test;
CREATE TABLE #test (id NVARCHAR(20), yr CHAR(4), mo CHAR(2), yr_mo CHAR(7), val int);

INSERT INTO
#test (id, yr, mo, yr_mo, val)
VALUES
('bob', '2023', '01', '2023_01', 100),
('bob', '2023', '02', '2023_02', 75),
('bob', '2023', '03', '2023_03', 0),
('bob', '2023', '04', '2023_04', 20),
('bob', '2023', '05', '2023_05', 60),
('jennifer', '2023', '01', '2023_01', 0),
('jennifer', '2023', '02', '2023_02', 10);

7 rows affected
DECLARE @start date = (SELECT MIN(DATEFROMPARTS(yr,mo,1)) FROM #test),
@end date = DATEFROMPARTS(YEAR(getdate()),MONTH(getdate()),1),
@select nvarchar(max),
@pivot nvarchar(max),
@sql nvarchar(max);

;WITH x AS
(
SELECT d = DATEADD(MONTH, delta, @start)
FROM
(
SELECT delta = ROW_NUMBER() OVER (ORDER BY @@SPID) - 1
FROM STRING_SPLIT(REPLICATE(',',DATEDIFF(MONTH, @start, @end)),',')
) AS y
),
cols AS
(
SELECT c = QUOTENAME(REPLACE(CONVERT(char(7),d,120), N'-', N'_'))
FROM x
)
SELECT @select = STRING_AGG(CONCAT(c,N' = COALESCE(',c,N',0)'),N','),
@pivot = STRING_AGG(c, N',')
FROM cols;

SET @sql = N'SELECT id, ' + @select + N'
FROM #test
PIVOT (SUM(val) FOR yr_mo IN (' + @pivot + N')) AS pvt;';

PRINT @sql;
EXEC sys.sp_executesql @sql;
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;