Run Code
|
API
|
Code Wall
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
sql server dynamic 12 month time series and pivot with month year column names
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
run
|
edit
|
history
|
help
0
exercise
Libros
1
Combined Where and having
megha
Sum then delete one row - SQL Server 2008 R2
Usage of joins
Libros
MERGE with OUTPUT
STACK