Doubt in conversion of rows into columns Sql Server 2012

Question

I turned rows into columns and need to improve this query by adding another row in column:

This is the query:

--****************************EMPRESA******************************
 if(Object_id('tempdb..#TBEmpresa') is not null)
    drop table #TBEmpresa
go  
 CREATE TABLE #TBEmpresa(
 EmpresaID int null,
 Empresa   varchar(50) null
 )
 go 
 insert into #TBEmpresa (EmpresaID, Empresa) values (1,'Vivo')
 insert into #TBEmpresa (EmpresaID, Empresa) values (2,'TIM')
 insert into #TBEmpresa (EmpresaID, Empresa) values (3,'Claro')
 go

 --*****************************SERVICOS***************************
if(OBJECT_ID('tempdb..#TBServico') is not null)
    drop table #TBServico
go
create table #TBServico(
ServicoID        int null,
DescricaoServico varchar(50) null
)
go

insert into #TBServico (ServicoID, DescricaoServico) values (1, 'Serviço de Dados')
insert into #TBServico (ServicoID, DescricaoServico) values (2, 'Serviço de TV')
insert into #TBServico (ServicoID, DescricaoServico) values (3, 'Servico de Internet')
insert into #TBServico (ServicoID, DescricaoServico) values (4, 'Servico de Voz')
insert into #TBServico (ServicoID, DescricaoServico) values (5, 'Servico de PABX')
go

--**********************SERVIÇO X EMPRESA *******************************
if(OBJECT_ID('tempdb..#TBServicoEmpresa') is not null)
    drop table #TBServicoEmpresa
go
create table #TBServicoEmpresa(
TBServicoEmpresaID int null,
TBEmpresaId        int null,
TBServicoId        int null
)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 1,1,1)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 2,1,2)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 3,1,3)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 4,1,4)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 5,1,5)

insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 6,2,1)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 7,2,2)

insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 8,2,3)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values ( 9,2,4)
insert into #TBServicoEmpresa (TBServicoEmpresaID, TBEmpresaId, TBServicoId) values (10,2,5)

--**********************PROCESSAMENTO************************************
if (OBJECT_ID('tempdb..#TBProcessamento') is not null)
    drop table #TBProcessamento
go
create table #TBProcessamento(
ProcessamentoID int null,
DataProcesso    DateTime null
)
go
insert into #TBProcessamento (ProcessamentoID, DataProcesso) values (1, '2017-06-22 00:00:00.000')
insert into #TBProcessamento (ProcessamentoID, DataProcesso) values (2, '2017-06-23 00:00:00.000')
insert into #TBProcessamento (ProcessamentoID, DataProcesso) values (3, '2017-06-24 00:00:00.000')
--***********************GRADE********************************************************************
if(OBJECT_ID('tempdb..#TBGrade') is not null)
    drop table #TBGrade
go
create table #TBgrade(
GradeId         int null,
EmpresaId       int null,
ServicoId       int null,
ProcessamentoId int null,
Data            DateTime null,
Situacao        varchar(50) null
)
go
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 1,1,1,1, 'EM USO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 2,1,1,2, 'DESCONTINUADO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 3,1,1,3, 'EM PROCESSO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 4,1,1,4, 'EM USO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 5,1,1,5, 'EM USO', '2017-06-22 00:00:00.000')

insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 6,1,2,1, 'DESCONTINUADO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 7,1,2,2, 'EM USO', '2017-06-22 00:00:00.000')

insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 8,1,3,3, 'DESCONTINUADO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values ( 9,1,3,4, 'EM PROCESSO', '2017-06-22 00:00:00.000')
insert into #TBGrade (GradeId, ProcessamentoId, EmpresaId, ServicoId, Situacao, Data) values (10,1,3,5, 'EM PROCESSO', '2017-06-22 00:00:00.000')
go
---*************************************************************************************************************************************

if(OBJECT_ID('tempdb..#TBResultado') is not null)
    drop table #TBResultado
go

select GRD.ProcessamentoId, EMP.EmpresaID, EMP.Empresa, SRV.ServicoID, SRV.DescricaoServico, GRD.Situacao, GRD.Data
into #TBResultado
from #TBEmpresa as EMP
    inner join #TBgrade as GRD
        on GRD.EmpresaID = EMP.EmpresaID
    inner join #TBServicoEmpresa as SRVEMP
        on GRD.EmpresaId = SRVEMP.TBEmpresaId
    inner join #TBServico as SRV
        on SRV.ServicoID = GRD.ServicoId
GROUP BY GRD.ProcessamentoId, EMP.EmpresaID, EMP.Empresa, SRV.ServicoID, SRV.DescricaoServico, GRD.Situacao, GRD.Data

DECLARE @COLUNAS VARCHAR(MAX)
SET @COLUNAS = ''
SELECT @COLUNAS = COALESCE(@COLUNAS + '[' + (CAST(DescricaoServico AS NVARCHAR(255))) + '],','')
FROM (SELECT DISTINCT DescricaoServico FROM #TBResultado) AS DADOS_HORIZONTAIS
SET @COLUNAS = LEFT (@COLUNAS, LEN(@COLUNAS)-1)
DECLARE @SQLSTRING NVARCHAR(1200);
SET @SQLSTRING = N'
SELECT * FROM(SELECT ProcessamentoId, EmpresaID, Empresa, ServicoID, DescricaoServico, Situacao,  Convert(varchar(10), Data,103) as [DATA] FROM #TBResultado) AS DADOS_HORIZONTAIS
PIVOT(MAX(Situacao) FOR DescricaoServico IN('+@COLUNAS+')) AS PivotTable ORDER BY DATA DESC;'

EXECUTE SP_EXECUTESQL @SQLSTRING

Answer 1

Here is the suggested code to get the report.

-- código #1
-- monta nome das colunas
declare @colID varchar(200), @colDescID varchar(4000);
set @colID= '';
set @colDescID= '';

SELECT @colID+= '[' + cast(ServicoID as varchar(3)) + '],',
       @colDescID+= '[' + cast(ServicoID as varchar(3)) + '] as [' + 
                     cast(ServicoID as varchar(3)) + ' - ' + DescricaoServico + '], '
  from TBServico
  order by ServicoID;
set @colID= left(@colID, len(@colID)-1);
set @colDescID= left(@colDescID, len(@colDescID)-1);  


-- monta comando SQL dinâmico
declare @comandoSQL varchar(8000);
set @comandoSQL= 
'with cteResultado as ( ' +
'SELECT GRD.ProcessamentoId, GRD.Data, SRVEMP.EmpresaID, SRVEMP.ServicoID, GRD.Situacao ' +
'from TBgrade as GRD ' +
'    inner join TBServicoEmpresa as SRVEMP on SRVEMP.ServicoEmpresaId = GRD.ServicoEmpresaId ' +
'    inner join TBProcessamento as PRC on PRC.ProcessamentoId = GRD.ProcessamentoId ' +
'), ' +
'cteP as ( ' +
'SELECT ProcessamentoID, Data, EmpresaID, ' + @colID +
'  from cteResultado ' +
'       pivot (max(Situacao) for ServicoID in (' + @colID + ')) as P ' +
') ' +
'SELECT P.ProcessamentoID, convert(char(10), P.Data, 103) as Data, ' +
'       P.EmpresaID, EMP.Empresa, ' + @colDescID +
'  from cteP as P ' +
'    inner join TBEmpresa as EMP on EMP.EmpresaId = P.EmpresaId;';

-- 
PRINT @comandoSQL
--EXECUTE (@comandoSQL)

---

Note that the declaration of the tables and the load commands have been revised, with the addition of reference integrities, primary keys etc.

-- código #2
set nocount on

if OBJECT_ID('TBGrade') is not null
    drop table TBGrade;

if OBJECT_ID('TBServicoEmpresa', 'U') is not null
    drop table TBServicoEmpresa;

if Object_id('TBEmpresa') is not null
    drop table TBEmpresa;

if Object_id('TBServico') is not null
    drop table TBServico;

if (OBJECT_ID('TBProcessamento') is not null)
    drop table TBProcessamento;
go

       --****************************EMPRESA******************************
 CREATE TABLE TBEmpresa(
    EmpresaID int not null primary key,
    Empresa   varchar(50) not null
 )
 go 
 insert into TBEmpresa (EmpresaID, Empresa) values 
    (1,'Vivo'), (2,'TIM'), (3,'Claro');
 go

 --*****************************SERVICOS***************************
create table TBServico(
   ServicoID        int not null primary key,
   DescricaoServico varchar(50) not null
)
go

insert into TBServico (ServicoID, DescricaoServico) values 
   (1, 'Serviço de Dados'),
   (2, 'Serviço de TV'),
   (3, 'Servico de Internet'),
   (4, 'Servico de Voz'),
   (5, 'Servico de PABX');
go

--**********************SERVIÇO X EMPRESA *******************************
create table TBServicoEmpresa(
   ServicoEmpresaID int not null primary key,
   EmpresaId        int not null references TBEmpresa,
   ServicoId        int not null references TBServico
);

insert into TBServicoEmpresa (ServicoEmpresaID, EmpresaId, ServicoId) values 
  (1,1,1), (2,1,2), (3,1,3), (4,1,4), (5,1,5), (6,2,1),
  (7,2,2), (8,2,3), (9,2,4), (10,2,5);


  --**********************PROCESSAMENTO************************************
create table TBProcessamento(
   ProcessamentoID int not null primary key,
   DataProcesso    Date not null
);
go

set dateformat ymd;
insert into TBProcessamento (ProcessamentoID, DataProcesso) values 
     (1, '2017-06-22'),
     (2, '2017-06-23'),
     (3, '2017-06-24');


--***********************GRADE********************************************************************

create table TBgrade(
   GradeId         int not null primary key,
   ServicoEmpresaId int not null references TBServicoEmpresa,
   ProcessamentoId int not null references TBProcessamento,
   Data            Date not null,
   Situacao        varchar(50) not null
);
go

insert into TBGrade (GradeId, ServicoEmpresaId, ProcessamentoId, Situacao, Data) values 
   (1,1,1, 'EM USO', '2017-06-22'),
   (2,2,1, 'DESCONTINUADO', '2017-06-22'),
   (3,3,1, 'EM PROCESSO', '2017-06-22'),
   (4,4,1, 'EM USO', '2017-06-22'),
   (5,5,1, 'EM USO', '2017-06-22'),
   (6,6,1, 'DESCONTINUADO', '2017-06-22'),
   (7,7,1, 'EM USO', '2017-06-22'),
   (8,8,1, 'DESCONTINUADO', '2017-06-22'),
   (9,9,1, 'EM PROCESSO', '2017-06-22'),
   (10,10,1, 'EM PROCESSO', '2017-06-22');   
go