Dynamic Case Pivot For Monthly Aggregated Data

  
 
 Declare @FromDate date='2018-01-16'
Declare @ToDate date='2018-05-30'


create table #Main (ID varchar(100),Date_ date,Amount Decimal(18,2))

insert into #Main(ID,Date_,Amount) values ('1001','2018-01-01',1500.00)
insert into #Main(ID,Date_,Amount) values ('1001','2018-03-05',100.00)
insert into #Main(ID,Date_,Amount) values ('1001','2018-05-14',2300.00)
insert into #Main(ID,Date_,Amount) values ('1001','2018-07-11',1025.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-01-18',500.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-02-04',700.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-05-26',3000.00)
insert into #Main(ID,Date_,Amount) values ('1002','2018-11-11',355.00)



 
Declare @sqlCase as NVarchar(4000) =null
Declare @sql as NVarchar(4000)=null
 
  
declare @ColumnHeaders VARCHAR(MAX), @ColumnHeaders1 VARCHAR(MAX);
--****  create a Number table
;WITH Num1 (n) AS (
SELECT 1 as n
UNION ALL SELECT n+1 as n
FROM Num1 Where n <101),
Num2 (n) AS (SELECT 1 FROM Num1 AS X, Num1 AS Y), 
Nums (n) AS (SELECT ROW_NUMBER() OVER(ORDER BY n) FROM Num2)
 
 ,alldates as (
select n, dateadd(month,n-1,@FromDate) dt from Nums 
WHERE dateadd(month,n-1,@FromDate)='''+Convert(varchar(10),@FromDate ,112)+''' and Date_<= '''+Convert(varchar(10),@ToDate ,112)
+''' ) t Group by grouping sets(ID,()) ';
     
--print @sql
EXEC(@SQL)
  

 
drop table #Main


 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e470ea84-41ab-4c26-9794-d7d72591122b/sum-of-monthly-with-total-column?forum=transactsql

Advertisements