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(
@tablenamevarchar(250),
@pivotColumnvarchar(250),
@groupByvarchar(8000),
@aggregateColumnsvarchar(250),
@aggregationvarchar(250),
@executeint = 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;