Dynamic PIVOT table SQL Server 2012 with COUNT() and SUM() and a TOTAL column

Question

What I'm trying to do (the name of the column is passed dynamically, but I hard-coded it, so it seems simpler in this question):

I am trying to query a database using PIVOT tables to sum one field and count the rows of a SQL Server 2012 table, but, in addition, I am trying to retrieve totals to the COUNT() and SUM() functions.

Normally, a pivot table would look like this (which is simpler than what I am trying to reach):

declare @campos nvarchar (max)
select @campos  = coalesce(@campos + ',[' + Setor + ']', '[' + Setor + ']')
from dbo.TbFinanciamentos
group by Setor
order by Setor

declare @resultado nvarchar(max)
set @resultado =
        'select * from(select Setor, ''br-'' + lower(UF_FILIAL) as [hc-key] from dbo.TbFinanciamentos) a
		 pivot(
		     count(Setor)
			 for Setor in(' + @campos + ')
		 ) a'

execute(@resultado)

But, I wanted to include the total (grand total) as a column, than I followed this tutorial and everything went alright.

What I have so far (that's working):

declare @campos nvarchar (max)
select @campos  = coalesce(@campos + ',[' + Setor + ']', '[' + Setor + ']')
from dbo.TbFinanciamentos
group by Setor
order by Setor

declare @total nvarchar(max)
select @total = coalesce(@total + 'isnull([' + Setor + '], 0) + ', 'isnull([' + Setor + '], 0) + ')
from dbo.TbFinanciamentos group by Setor order by Setor
set @total = left(@total, len(@total) - 1)

declare @resultado nvarchar(max)
set @resultado =
        'select *, '+ @total +' as [value] into #temp_total
         from (select Setor, ''br-'' + lower(UF_FILIAL) as [hc-key] from dbo.TbFinanciamentos) a
		 pivot(
		     count(Setor)
			 for Setor in(' + @campos + ')
		 ) b
         select * from #temp_total'

execute(@resultado)

But, as I did for COUNT(), I would like to do for the SUM() and retrieve both in the same query.

What I tried so far to do what I aim to:

1. I duplicated the PIVOT part and tried to do a FULL OUTER JOIN, but the problem is that it is repeating the columns to the final result what generates an error (Invalid column name). When I print @resultado, there it is, columns are duplicating.

declare @campos nvarchar (max)
select @campos  = coalesce(@campos + ',[' + Setor + ']', '[' + Setor + ']')
from dbo.TbFinanciamentos
group by Setor
order by Setor

declare @total nvarchar(max)
select @total = coalesce(@total + 'isnull([' + Setor + '], 0) + ', 'isnull([' + Setor + '], 0) + ')
from dbo.TbFinanciamentos group by Setor order by Setor
set @total = left(@total, len(@total) - 1)

declare @resultado nvarchar(max)
set @resultado =
        'select *, '+ @total +' as [value] into #temp_total
         from (
		     (select Setor, ''br-'' + lower(UF_FILIAL) as [hc-key] from dbo.TbFinanciamentos
				 pivot(
					 count(Setor)
					 for Setor in(' + @campos + ')
				 ) as b
			 ) as sth
			full outer join
			(
		     select cast(Valor_do_Emprestimo as float) as Valor_do_Emprestimo, Setor, ''br-'' + lower(UF_FILIAL) as [hc-key] from dbo.TbFinanciamentos
				 pivot(
					 count(Setor)
					 for Setor in(' + @campos + ')
				 ) as b
			 ) as sth_else
			on sth.[hc-key] = sth_else.[hc-key]
		 )
         select * from #temp_total'

execute(@resultado)

2. I tried to UNPIVOT and PIVOT method which generates an error of invalid columns as well.

Answer 1

So needless to say doing anything dynamic is highly problematic since you never really get a handle on your metadata. And in any case it's more acceptable when you have multiple conditional aggregations like this to coalesce your different measures using CASE statements, something like

    SUM(CASE When Setor = ''' + Setor ''' then 1 else 0 end) 
as [' + Setor + '_Count], 
SUM(CASE When Setor = ''' + Setor ''' then Valor_do_Emprestimo else 0 end) 
as [' + Setor + '_Total],'

and just build up a single query this way against your dataset.

Anyway, to answer your specific issue, if you want to combine these two you have to provide unique column names which means you need to create slightly different versions of @campos and @total. Here I've just done @campos to give you the idea.

Notice I also had to change hc_key to hc_key2 in the second pivot to also avoid duplicate column names.

declare @campos nvarchar (max)
select @campos  = coalesce(@campos + ',[' + Setor + ']', '[' + Setor + ']')
from dbo.TbFinanciamentos
group by Setor
order by Setor

declare @campos2 nvarchar (max)
select @campos2  = coalesce(@campos2 + ',[' + Setor + '_2]', '[' + Setor + '_2]')
from dbo.TbFinanciamentos
group by Setor
order by Setor

declare @total nvarchar(max)
select @total = coalesce(@total + 'isnull([' + Setor + '], 0) + ', 'isnull([' + Setor + '], 0) + ')
from dbo.TbFinanciamentos group by Setor order by Setor
set @total = left(@total, len(@total) - 1)

declare @resultado nvarchar(max)
set @resultado =
        'select * into #temp_total from (
        select *, '+ @total +' as [value]          from
        (
             select * from (select Setor, ''br-'' + lower(UF_FILIAL) as [hc-key] from dbo.TbFinanciamentos) pvt
                 pivot(
                     count([Setor])
                     for Setor in(' + @campos + ')
                 ) as b
             ) as sth
            full outer join
            (
               select *          from (
             select * from (select cast(Valor_do_Emprestimo as float) as Valor_do_Emprestimo, Setor+''_2'' as Setor, ''br-'' + lower(UF_FILIAL) as [hc-key2] from dbo.TbFinanciamentos ) pvt
                 pivot(
                    sum([Valor_do_Emprestimo])
                     for Setor in(' + @campos2 + ')
                 ) as b
             ) c
            ) as sth_else
            on sth.[hc-key] = sth_else.[hc-key2]
         ) d

         select * from #temp_total'
        execute(@resultado)