Run Code
|
API
|
Code Wall
|
Users
|
Misc
|
Feedback
|
Login
|
Theme
|
Privacy
|
Patreon
sql server dynamic 12 month time series and pivot with month year colu...
Language:
Ada
Assembly
Bash
C#
C++ (gcc)
C++ (clang)
C++ (vc++)
C (gcc)
C (clang)
C (vc)
Client Side
Clojure
Common Lisp
D
Elixir
Erlang
F#
Fortran
Go
Haskell
Java
Javascript
Kotlin
Lua
MySql
Node.js
Ocaml
Octave
Objective-C
Oracle
Pascal
Perl
Php
PostgreSQL
Prolog
Python
Python 3
R
Rust
Ruby
Scala
Scheme
Sql Server
Swift
Tcl
Visual Basic
Layout:
Vertical
Horizontal
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
View schema
Execution time: 0,08 sec, rows selected: 11, rows affected: 33, absolute service time: 0,25 sec
edit mode
|
history
|
discussion
id
Nov 2015
Dec 2015
Jan 2016
Feb 2016
Mar 2016
Apr 2016
May 2016
Jun 2016
Jul 2016
Aug 2016
Sep 2016
Oct 2016
1
1
0
0
1
0
0
0
0
0
0
0
0
1
2
2
1
0
0
1
0
0
0
0
0
0
0
0
3
3
0
1
0
0
1
0
0
0
0
0
0
0
4
4
0
0
0
0
0
1
0
0
0
0
0
0
5
5
0
0
0
0
0
0
1
0
0
0
0
0
6
6
0
0
0
0
0
0
0
1
0
0
0
0
7
7
0
0
0
0
0
0
0
0
1
0
0
0
8
8
0
0
0
0
0
0
0
0
0
1
0
0
9
9
0
0
0
0
0
0
0
0
0
0
1
0