mercredi 6 mai 2015

Sum Colum case () after pivot

Sorry if I don´t explain very well, and the title isn´t very clear. I´m using SQL Server 2005. I have a query with Pivot that is working fine, but know I must add a new query that get resuls from quarterly.

This is my query to get result from Month

    WITH PivotData AS
(
SELECT  idWatimetro, mes,ano, valor
FROM   E_Registros_Watimetros where ano = 2012
)
SELECT *
FROM PivotData 
PIVOT(SUM(valor) FOR idWatimetro IN ([1],[2],[3],[4],[5] AS P order by mes;

enter image description here

So, know only I want to get four registres

1 With Month 1+2+3
2 Wint Month 4+5+6
3 With Month 7+8+9
4 Wint Month 10+11+12

enter image description here

I have been trying with UNION ALL but don´t work as I expected, Any help will be gratefull, and sorry for my bad english and explanation.

This is my query without result

SELECT  * 
FROM    (
            SELECT  idWatimetro, ano,mes, valor
            FROM E_Registros_Watimetros   
            WHERE (ano = 2012 and mes = 1 ) or (ano = 2012 and mes = 2)or (ano = 2012 and mes = 3)
            UNION ALL
            SELECT  idWatimetro, ano,mes, valor
            FROM E_Registros_Watimetros    
            WHERE (ano = 2012 and mes = 4 ) or (ano = 2012 and mes = 5)or (ano = 2012 and mes = 6)
        ) AS SourceTable
PIVOT(SUM(valor) FOR idWatimetro IN ([1],[2],[3],[4],[5]))AS P

Thanks advanced.

Aucun commentaire:

Enregistrer un commentaire