sql server dynamic 12 month time series and pivot with month year column names, Sql Server

Language: Layout:

CREATE TABLE Bexample
    ([ID] int)
;
    
INSERT INTO Bexample
    ([ID])
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9)
;

CREATE TABLE Aexample
    ([ID] int, [B_PK] int, [SOME_DT] datetime)
;
    
INSERT INTO Aexample
    ([ID], [B_PK], [SOME_DT])
VALUES
    (1, 1, '2015-01-01 00:00:00'),
    (2, 2, '2015-02-01 00:00:00'),
    (3, 3, '2015-03-01 00:00:00'),
    (4, 4, '2015-04-01 00:00:00'),
    (5, 5, '2015-05-01 00:00:00'),
    (6, 6, '2015-06-01 00:00:00'),
    (7, 7, '2015-07-01 00:00:00'),
    (8, 8, '2015-08-01 00:00:00'),
    (9, 9, '2015-09-01 00:00:00'),
    (10, 1, '2015-10-01 00:00:00'),
    (11, 2, '2015-11-01 00:00:00'),
    (12, 3, '2015-12-01 00:00:00'),
    (13, 1, '2016-01-01 00:00:00'),
    (14, 2, '2016-02-01 00:00:00'),
    (15, 3, '2016-03-01 00:00:00'),
    (16, 4, '2016-04-01 00:00:00'),
    (17, 5, '2016-05-01 00:00:00'),
    (18, 6, '2016-06-01 00:00:00'),
    (19, 7, '2016-07-01 00:00:00'),
    (20, 8, '2016-08-01 00:00:00'),
    (21, 9, '2016-09-01 00:00:00'),
    (22, 1, '2016-10-01 00:00:00'),
    (23, 2, '2016-11-01 00:00:00'),
    (24, 3, '2016-12-01 00:00:00')
;

DECLARE @cols AS VARCHAR(MAX)
DECLARE @query  AS VARCHAR(MAX)

;with mylist as (
      select DATEADD(month,-12, DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) ) as [mnth]
      union all 
      select DATEADD(month,1,[mnth]) 
      from mylist
      where [mnth] < DATEADD(month,-1, DATEADD(month, DATEDIFF(month,0,GETDATE()), 0) )
      )
select [mnth]
into #mylist
from mylist

SELECT @cols = STUFF((SELECT ',' + QUOTENAME(format([mnth],'MMM yyyy'))
                      FROM #mylist
                      FOR XML PATH(''), TYPE
                      ).value('.', 'NVARCHAR(MAX)')
                  ,1,1,'')

SET @query = 'SELECT id, ' + @cols + ' FROM
                (
                    select
                          format([mnth],''MMM yyyy'') colname
                        , b.id
                        , a.b_pk
                    from #mylist
                    cross join bexample b
                    left join aexample a on #mylist.mnth = DATEADD(month, DATEDIFF(month,0,a.some_dt), 0)
                               and b.id = a.b_pk
               ) sourcedata
                pivot
                (
                    count([b_pk])
                    FOR [colname] IN (' + @cols + ')
                ) p '

--select @query -- use select to inspect the generated sql
--execute(@query) -- once satisfied that sql is OK, use execute
                    
                    
drop table #mylist
drop table Bexample
drop table Aexample

​x
                    left join aexample a on #mylist.mnth = DATEADD(month, DATEDIFF(month,0,a.some_dt), 0)
 
CREATE TABLE Bexample
    ([ID] int)
;
    
INSERT INTO Bexample
    ([ID])
VALUES
    (1),
    (2),
    (3),
    (4),
    (5),
    (6),
    (7),
    (8),
    (9)
;
​
​
CREATE TABLE Aexample
    ([ID] int, [B_PK] int, [SOME_DT] datetime)
;
    
INSERT INTO Aexample
    ([ID], [B_PK], [SOME_DT])
VALUES
    (1, 1, '2015-01-01 00:00:00'),
    (2, 2, '2015-02-01 00:00:00'),
    (3, 3, '2015-03-01 00:00:00'),
    (4, 4, '2015-04-01 00:00:00'),
    (5, 5, '2015-05-01 00:00:00'),
    (6, 6, '2015-06-01 00:00:00'),
    (7, 7, '2015-07-01 00:00:00'),
    (8, 8, '2015-08-01 00:00:00'),
    (9, 9, '2015-09-01 00:00:00'),
    (10, 1, '2015-10-01 00:00:00'),
    (11, 2, '2015-11-01 00:00:00'),

View schema
Execution time: 0,08 sec, rows selected: 11, rows affected: 33, absolute service time: 0,25 sec

sql server dynamic 12 month time series and pivot with month year colu...