SQL SERVER Cross Reference for months

Question

SQL SERVER Cross Reference for months

Navigation

#1 by (0 votes)

1

I have this Cross Reference Consultation that gives me the units per month and year and it works perfectly, what I would like is to be able to add all the months in the same Row as Total, being able to add [1], [2], [3 ] .... At the end of the row. Thank you very much in advance

select anio, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
             [7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic, [13] Total
from (
    -- select inicial, a pivotar. Podría ser una tabla
    select  year(O.Fecha) as anio, month(O.Fecha) as Mes,
            D.Unitats as Cantidad
    from [Linpediv] D inner join Cabpediv O on D.Pedido = O.PEDIDO
    where O.Fecha between '20000101' and '20171231'
) V PIVOT ( sum(Cantidad) FOR Mes IN ([1], [2], [3], [4], [5],
        [6], [7], [8], [9], [10], [11], [12], [13]) ) as PT

sql-server

asked by Miquel 28.05.2017 в 11:00

source

1 answer

Compress image before uploading it to the server How to export to multiple worksheets in an ETL using Visual Studio [closed]

score 0 · Answer 1

For starters, the group by is missing. The basic query would be this:

select  year(O.Fecha) as anio,
        month(O.Fecha) as Mes,
        SUM(D.Unitats) as Cantidad
        from [Linpediv] D 
        inner join Cabpediv O 
             on D.Pedido = O.PEDIDO
        where O.Fecha between '20000101' and '20171231'
        group by year(O.Fecha),month(O.Fecha)

Now, one possibility is to add to this same query the total sum per year so that after PIVOT read it as column 13:

select  year(O.Fecha) as anio,
        month(O.Fecha) as Mes,
        SUM(D.Unitats) as Cantidad
        from [Linpediv] D 
        inner join Cabpediv O 
             on D.Pedido = O.PEDIDO
        where O.Fecha between '20000101' and '20171231'
        group by year(O.Fecha),month(O.Fecha)

 union

 select year(O.Fecha) as anio,
        13 as Mes,
        SUM(D.Unitats) as Cantidad
        from [Linpediv] D 
        inner join Cabpediv O 
             on D.Pedido = O.PEDIDO
        where O.Fecha between '20000101' and '20171231'
        group by year(O.Fecha)

The other simpler possibility is to add the 12 columns in the final selection:

select anio, [1] Ene, [2] Feb, [3] Mar, [4] Abr, [5] May, [6] Jun,
             [7] Jul, [8] Ago, [9] Sep, [10] Oct, [11] Nov, [12] Dic, 
             [1] + [2] + [3] + [4] + [5] + [6] + [7] + [8] + [9] + [10] + [11] + [12] Total