Error in SQL SERVER PIVOT

Asked

Viewed 45 times

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/

  • 1

    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.

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.