Make up table definition with [yr-mth] columns and column list

UDF Function

-- =============================================
-- Author: Albert He
-- Create date: 2018-05-18
-- Description:	make up columns for Automation Report
-- example: dbo.ufn_AR_makeupColumns('2017-08','2018-03','cast(sum([%1]) as varchar) [%1],',0)
-- =============================================
ALTER function [dbo].[ufn_AR_makeupColumnsTemp](@startMon varchar(20),@endMon varchar(20),@template varchar(max), @monthName bit=1) returns varchar(max) as
begin
	declare @i int, @sql varchar(max)='';
	declare @startYr int,@endYr int,@yr char(2),@mon int;
	declare @fld varchar(max)

	select @startYr=left(@startMon,4),@endYr=left(@endMon,4), @i = @startYr*12+right(@startMon,2),@yr=right(@startYr,2)

	while @i <= @endYr*12+right(@endMon,2) --@endYr=2018,@endMon=18-06
	begin
		set @mon = (@i-@startYr*12-1)%12+1

		set @fld = case when @monthName=1 then @yr+'-'+left(DateName(month, DateAdd(month, @mon, -1)),3) else @yr+'-'+ case when @mon<10 then '0' else '' end+cast(@mon as varchar) end
		set @sql += cast(replace(@template,'%1',@fld) as varchar(max))

		set @i+=1;
		if @mon%12=0 set @yr=cast(cast(@yr as int)+1 as varchar)
	end
	return @sql
end

Usage - definition

@sql+='create table #t0 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255)'+dbo.ufn_AR_makeupColumns('2017-01','2018-12',',[%1] float',0)+',Total float)

Result - definition

create table #t0 (LOB varchar(50),PROCESS_NAME varchar(255),seq varchar(255),[17-01] float,[17-02] float,[17-03] float,[17-04] float,[17-05] float,[17-06] float,[17-07] float,[17-08] float,[17-09] float,[17-10] float,[17-11] float,[17-12] float,[18-01] float,[18-02] float,[18-03] float,[18-04] float,[18-05] float,[18-06] float,[18-07] float,[18-08] float,[18-09] float,[18-10] float,[18-11] float,[18-12] float,Total float)

Usage - column list

select @strColumns = dbo.ufn_AR_makeupColumns('2017-01','2018-12',',[%1]',0)

Result - column list

,[17-01],[17-02],[17-03],[17-04],[17-05],[17-06],[17-07],[17-08],[17-09],[17-10],[17-11],[17-12],[18-01],[18-02],[18-03],[18-04],[18-05],[18-06],[18-07],[18-08],[18-09],[18-10],[18-11],[18-12]

Author: IT Team