0
TRANSFORM Sum(QUANTIDADE) AS SumOfQUANTIDADE
SELECT SUPERVISOR, Sum(QUANTIDADE) AS Total
FROM REPORTING
WHERE (((REPORTING.FAMILIA)="TESTE1" Or (REPORTING.FAMILIA)="TESTE2" In (select [00 - Max Data Report].[MaxOfCODMES_REPORT] from [00 - Max Data Report])))
GROUP BY COORDENADOR, SUPERVISOR
ORDER BY COORDENADOR
PIVOT Val([COD_DIA]);

columns to pick:

Quantidade COD_dia
1 2
1 2
2 12
5 13
3 16

desired result:

Supervisor 1 2 3 4
Supervisor1 43 55 12 81
Supervisor2 31 22 43 23
Supervisor3 54 67 77 55
Supervisor4 42 22 11 56

I am trying to convert this query to sql server. the above is the result from the query in access. the column headers are the months which are included in the cod_dia column from the table in the server. if we are in the end of the month then it would input the 31 columns for example.

select * from ( 
          select  SUPERVISOR,Sum(QUANTIDADE) AS Total 
                  FROM REPORTING 
                           where  FAMILIA='TESTE1' or FAMILIA='TESTE2' In (select [MaxOfCODMES_REPORT] from [00 - Max Data Report ESALES]) group by supervisor,coordenador) t pivot (Sum(QUANTIDADE) for QUANTIDADE in (cod_dia)) as p 

i tried this in sql server but it says it doesn't recognise "Quantidade" in the last line, but it should because if select that column from that database it shows that column. Unless, of course, i am doing something wrong in the query

Msg 207, Level 16, State 1, Line 10
Invalid column name 'QUANTIDADE'.
Msg 207, Level 16, State 1, Line 10
Invalid column name 'QUANTIDADE'.
2
  • Add a minimal reproducible example showing table structure, please.
    – Hannah Vernon
    Oct 10 at 17:03
  • hello i edited the post, i have a feeling i have to use dynamic sql because the transform from access picks the values automatically from cod_dia. something similar to this i think. stackoverflow.com/questions/52761674/sql-server-dynamic-pivot-for-an-unknow-number-of-columns
    – rafamaniac
    Oct 10 at 17:29

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.