Pivoting Row Values to column names With Dynamic number of columns

Question

I have data in SQL Server like the following


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);

I'd like to PIVOT the data so that the yr_mo row values become columns up until a specified point in time (like current month 2023_08). However I would like to pivot it so that the data fills in 0s up until the declared "stop date" which for the example above would be current date 2023_08 I'm looking for a solution that if I declared that dynamic value, I could create a n columns up until that date with the appropriate data.

Desired outcome sample:

DROP TABLE IF EXISTS #desired;
CREATE TABLE #desired (
    id NVARCHAR(20),
    [2023_01] int,
    [2023_02] int,
    [2023_03] int,
    [2023_04] int,
    [2023_05] int,
    [2023_06] int,
    [2023_07] int,
    [2023_08] int -- current month, would like to dynamically declare this
);

INSERT INTO #desired (
    id,
    [2023_01],
    [2023_02],
    [2023_03],
    [2023_04],
    [2023_05],
    [2023_06],
    [2023_07],
    [2023_08]
)
VALUES
    ('bob', 1, 100, 75, 0, 20, 60, 0, 0),
    ('jennifer', 0,  10,  0, 0,  0,  0, 0, 0);

My current attempt:


SELECT
    id,
    ISNULL(pvt.[2023_01], 0) AS '2023_01', 
    ISNULL(pvt.[2023_02], 0) AS '2023_02',
    ISNULL(pvt.[2023_03], 0) AS '2023_03',
    ISNULL(pvt.[2023_04], 0) AS '2023_04',
    ISNULL(pvt.[2023_05], 0) AS '2023_05',
    ISNULL(pvt.[2023_06], 0) AS '2023_06',
    ISNULL(pvt.[2023_07], 0) AS '2023_07',
    ISNULL(pvt.[2023_08],0) AS '2023_08'
FROM (
    SELECT id, yr_mo, val FROM #test
) AS src
PIVOT (
    SUM(val)
    FOR yr_mo in ([2023_01], [2023_02], [2023_03], [2023_04], [2023_05], [2023_06], [2023_07], [2023_08])
) AS pvt;

This is not dynamic as I have to list out every single month by hand.

What what's stopping you? What have you tried? Why isn't it working? — Thom A, Aug 30 '23 at 14:38
https://www.mssqltips.com/sqlservertip/2783/script-to-create-dynamic-pivot-queries-in-sql-server/ — Aaron Bertrand, Aug 30 '23 at 14:42
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Thom A, Aug 30 '23 at 14:53
['2023_01'] should be just [2023_01]. What's then left is to simulate your query by building the values dynamically. Usually one creates two variables, one for pivot part and one for select part. You take distinct yr_mo and then generate needed string — siggemannen, Aug 30 '23 at 14:54
Does this answer your question? [SQL Server dynamic PIVOT query?](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — siggemannen, Aug 30 '23 at 15:00

Aaron Bertrand · Answer 1 · 2023-08-30T15:06:49.403

Here's one way, using dynamic PIVOT techniques described in this article: Script to create dynamic PIVOT queries. This assumes you want the first month to be the earliest date in the table, but if you want that to be dynamic too (e.g. go 10 months back), you could change the way you calculate @start by using @end instead of the table.

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;

Demo in this db<>fiddle

Pivoting Row Values to column names With Dynamic number of columns

1 Answers1