Dynamic pivot with headers as Month name

Transact-SQL
1

Hi

I am trying to use dynamic pivot and need help on converting rows to columns dynamically. Please find below sample DDL and desired result.

Note: actually the main requirements is to get the 3 months average prior from the current month. In case I cannot handle this is script I will work on this in spotfire.

DECLARE	@Rawdata TABLE
	(
		ro_dt datetime,
		model varchar(10),
		ctype varchar(10),
		ro_status varchar(10),
		wrkctrtype varchar(20),
		result1 varchar(10)
	)
INSERT	@Rawdata VALUES	('2018-04-08 15:52:06.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-04-08 15:55:28.000','100','A','Failed','sof','fail')
INSERT	@Rawdata VALUES	('2018-04-08 16:03:32.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-04-08 17:52:06.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-05-08 15:55:28.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-05-10 16:03:32.000','100','A','Failed','sof','fail')
INSERT	@Rawdata VALUES	('2018-05-15 15:52:06.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-06-10 15:55:28.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-06-15 16:03:32.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-06-28 15:52:06.000','100','A','Failed','sell','fail')
INSERT	@Rawdata VALUES	('2018-07-08 15:55:28.000','100','A','Passed','Finished Good','Good')
INSERT	@Rawdata VALUES	('2018-07-08 16:03:32.000','100','A','Passed','Finished Good','Good')


  select
ro_dt_month,
model,
ctype,
sum(recovery_qty) / cast(sum(total_input) as decimal(10,2)) as Recovery_yield_pct
from
(
select 
		replace(substring(convert(varchar(50),ro_dt,111),1,7),'/','-') ro_dt_month,
		model,
		ctype,
		total_input=sum(case when ro_status in ('Passed','Failed') then 1 else 0 end),
		passed_qty = sum(case when  ro_status ='Passed' then 1 else 0 end),
		recovery_qty =sum(case when  ro_status ='Passed' and wrkctrtype='Finished Good' then 1 else 0 end),
		scrap_qty =sum(case when  ro_status ='Failed' and result1='fail' then 1 else 0 end)
from	@Rawdata
group by
		replace(substring(convert(varchar(50),ro_dt,111),1,7),'/','-'),
		model,
		ctype
)t

Expected result:
Desired Result:

Model----ctype----2018-04----2018-05----------2018-06----------2018-07---avg_prev_3_months
100------A--------0.75--------0.666666667-----0.666666667-------1 ------0.69444

2

Perhaps something like this:

declare @sql nvarchar(2048)='
select model
      ,ctype
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)-2,0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-3,0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-2,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)-1,0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-2,0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-1,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp),0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-1,0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp),0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)+1,0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp),0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp),0)
              then 1
              else 0
           end
          )
      /3.0
       as avg_3_months
  from rawdata
 where ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
   and ro_dt<dateadd(month,datediff(month,0,current_timestamp)+1,0)
   and ro_status=''Passed''
 group by model
         ,ctype
;
';
/*print @sql;*/
execute sp_executesql @sql;

Edit: Hmm, it's really confusing when you keep changing your post. What is the final result you want to see?

3

Sorry bistmed. I change the post because its not clear the sample data. I'm trying to work on this in report but I'm not successful. I need to add column after the calculated column that will be my previous 3 months average. so my option is to handle it in script. I'm looking for a dynamics approach. because it can be adding more different date transaction.

4

Try this:

declare @sql nvarchar(4000)='
select model
      ,ctype
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)-2,0)
               and ro_status=''Passed''
              then 1
              else 0
           end
          )
      *1.0
      /sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)-2,0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-3,0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-2,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)-1,0)
               and ro_status=''Passed''
              then 1
              else 0
           end
          )
      *1.0
      /sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-2,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)-1,0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-2,0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-1,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp),0)
               and ro_status=''Passed''
              then 1
              else 0
           end
          )
      *1.0
      /sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-1,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp),0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp)-1,0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp),0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)+1,0)
               and ro_status=''Passed''
              then 1
              else 0
           end
          )
      *1.0
      /sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp),0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp)+1,0)
              then 1
              else 0
           end
          )
       as ['+datename(month,dateadd(month,datediff(month,0,current_timestamp),0))+']
      ,sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp),0)
               and ro_status=''Passed''
              then 1
              else 0
           end
          )
      *1.0
      /sum(case
              when ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
               and ro_dt<dateadd(month,datediff(month,0,current_timestamp),0)
              then 1
              else 0
           end
          )
       as avg_3_months
  from rawdata
 where ro_dt>=dateadd(month,datediff(month,0,current_timestamp)-3,0)
   and ro_dt<dateadd(month,datediff(month,0,current_timestamp)+1,0)
 group by model
         ,ctype
;
';
/*print @sql;*/
execute sp_executesql @sql;