Dynamic pivoting

--sample data
set nocount on;
drop table  master.dbo.Invoice
go
create table  master.dbo.Invoice([Department] varchar(50), [Year] int, [Month] int, Amount int);
go
declare @y int = 2010,@m int = 1, @dp int = 1;
while @y <= 2014
begin;
	set @m = 1;
	while @m <= 12
	begin;
		if @m <= 3
		set @dp = 1;
		else if @m <= 6
		set @dp = 2;
		else if @m <= 12
		set @dp = 3;		 
		insert into  master.dbo.Invoice
		values
		('Department '+cast(@dp as varchar(2)), @y,@m,(@y*@m)/10);
		set @m = @m+1;
	end;
	set @y = @y+1;
end;
go
select * from master.dbo.Invoice
go
--run as sql script
declare
	@tablename varchar(250) = '[master].[dbo].[Invoice]',
	@pivotColumn varchar(250) = '[month]',
	@groupBy varchar(8000) = '[year]',
	@aggregateColumns varchar(250) = '[Amount]',
	@aggregation varchar(250) = 'SUM([Amount])',
	@execute int = 0

declare
	@sql varchar(8000),
	@pivotcols varchar(8000) = '';
declare @result table(result varchar(8000));

set @sql = '
declare
	@pivotcols varchar(8000) = '''';
with cte as (
select distinct '+@pivotColumn+', ''[''+cast('+@pivotColumn+' as varchar(250))+'']'' col
from '+@tablename+')
select @pivotcols = @pivotcols + col + '',''
from cte
order by '+@pivotColumn+';
set @pivotcols = left(@pivotcols, len(@pivotcols)-1);
select @pivotcols result';
insert into @result
exec (@sql);
select @pivotcols = result
from @result;

set @sql = '
SELECT *
FROM (
    SELECT 
       '+@groupBy+', '+@pivotColumn+', '+@aggregateColumns+' 
    FROM '+@tablename+'
) AS s
PIVOT
(
    '+@aggregation+'
    FOR '+@pivotColumn+' IN ('+@pivotcols+')
)AS p;';

if @execute = 0
print (@sql);
else exec (@sql);
go

--package into stored procedure and execute
create procedure dbo.dynamicPivot(
	@tablename			varchar(250),
	@pivotColumn		varchar(250),
	@groupBy			varchar(8000),
	@aggregateColumns	varchar(250),
	@aggregation		varchar(250),
	@execute			int = 0)
as
begin;

declare
	@sql varchar(8000),
	@pivotcols varchar(8000) = '';
declare @result table(result varchar(8000));

set @sql = '
declare
	@pivotcols varchar(8000) = '''';
with cte as (
select distinct '+@pivotColumn+', ''[''+cast('+@pivotColumn+' as varchar(250))+'']'' col
from '+@tablename+')
select @pivotcols = @pivotcols + col + '',''
from cte
order by '+@pivotColumn+';
set @pivotcols = left(@pivotcols, len(@pivotcols)-1);
select @pivotcols result';
insert into @result
exec (@sql);
select @pivotcols = result
from @result;

set @sql = '
SELECT *
FROM (
    SELECT 
       '+@groupBy+case @groupBy when '' then '' else ', ' end +@pivotColumn+', '+@aggregateColumns+' 
    FROM '+@tablename+'
) AS s
PIVOT
(
    '+@aggregation+'
    FOR '+@pivotColumn+' IN ('+@pivotcols+')
)AS p;';

if @execute = 0
print (@sql);
else exec (@sql);
end;
go

exec dbo.dynamicPivot
	@tablename			= '[master].[dbo].[Invoice]',
	@pivotColumn		= '[month]',
	@groupBy			= '[Department],[Year]',
	@aggregateColumns	= '[Amount]',
	@aggregation		= 'SUM([Amount])',
	@execute			= 1;
go

--drop created objects
/*
drop table dbo.Invoice;
go
drop procedure dbo.dynamicPivot;
go
*/

set nocount off;