1
Guys, I have a problem in the structure of my PIVOT.
DECLARE @IdGiaContribuinte INT = 0,
@IdGrupoContribuinte INT = 0,
@Exercicio INT = 2017,
@IdVafvalorAdicionadoTipo INT = 3
SELECT U.Exercicio,
U.IdGiaContribuinte,
U.InscricaoEstadual,
U.Cnpj,
Ranking = CAST(U.Ranking AS VARCHAR) + '°',
U.Variacao
FROM (
SELECT Contribuinte = Gia.IdGiaContribuinte,
ContribuinteIE = Gia.InscricaoEstadual,
ContribuinteCNPJ = Gia.Cnpj,
Ano = Vaf.Exercicio,
Va = Vaf.ValorAdicionado,
Vaf.Variacao
FROM VafEmpresaResumoVA Vaf
INNER JOIN GiaContribuinte Gia ON Gia.IdGiaContribuinte = Vaf.IdGiaContribuinte
WHERE Exercicio IN (2017,2016,2015,2014)
AND Vaf.IdVafValorAdicionadoTipo = @IdVafValorAdicionadoTipo
AND (Vaf.IdGiaContribuinte = @IdGiaContribuinte OR 0 = @IdGiaContribuinte)
AND (
(@IdGrupoContribuinte = 0)
OR
EXISTS(SELECT 1
FROM GrupoContribuinteItem Gci
WHERE Gci.IdGiaContribuinte = Vaf.IdGiaContribuinte
AND Gci.IdGrupoContribuinte = @IdGrupoContribuinte)
)
) AS A
PIVOT (
SUM(A.Va) FOR
A.Ano IN (Exercicio,IdGiaContribuinte, InscricaoEstadual, Cnpj, ValorAdicionado, Ranking, VariacaoPercentual)
) AS U
ORDER BY Exercicio, InscricaoEstadual
The same is returning:
Mensagem 8114, Nível 16, Estado 1, Linha 38
Error converting data type nvarchar to int.
Mensagem 473, Nível 16, Estado 1, Linha 38
The incorrect value "Exercicio" is supplied in the PIVOT operator.
I am now using this sql server function, I still have doubts about the structure, basically need to transform the value of rows to columns.
Regarding pivot, I suggest reading the article Alas & Pivots -> https://portosql.wordpress.com/artigos/
– José Diz
The error message informs that an error has occurred in a text data type conversion (nvarchar) to numeric (int). Probably an implicit conversion. Note that there is no column with the name "Exercise" in the subconsulta. I suggest you add details about what is the purpose of the query, tables involved etc.
– José Diz