Questions with Pivot SQL Server

Questions with Pivot SQL Server

Good morning people

I’m trying to pivot the table below with the sql server using the VALOR1 Column and ignoring the VALOR2 and VALOR3 column:

Before

After

SqlQuery

    SELECT ANO
, [1] AS JANEIRO
         , [2] AS FEVEREIRO
         , [3] AS MARÇO

FROM VENDAANUAIS3 PIVOT (MAX(VALOR1)
FOR MES IN ([1],[2],[3]))P
ORDER BY 1;

The problem is that I realized that in order to have one row per year, the VALOR2 and VALOR3 fields must be equal, and sometimes they must be different as the example below:

I’m trying to do one row per year

it is possible to perform a query that always returns one row per year, ignoring the column values VALOR2 and VALOR3?

Thank you for your help

Answer

Select only the required column for pivot using a derived table or cte

 SELECT   ANO
        , [1] AS JANEIRO
        , [2] AS FEVEREIRO
        , [3] AS MARÇO

FROM (
          SELECT ANO, MES, VALOR1
          FROM   VENDAANUAIS3 
     ) V
     PIVOT 
     (
          MAX(VALOR1)
          FOR MES IN ([1],[2],[3])
     ) P