CODEWITHSUNDEEP
sqlsql-server-2008pivot
Saifuddin
Saifuddin

Reputation: 99

Column Total at the end of SQL Pivot query

I have generated the following SQL Server 2008 pivot, and it gives me desire result. I want to add total column at end of pivot, where I'm finding it difficult.

Please find the SQL I'm using for pivot

Select * from (
     Select Case when (podocstatus = 'CL') then 'Closed PO'
         when (podocstatus = 'OP') then 'Open PO'
         when (podocstatus = 'SC') then 'Short Closed PO'   
    end as POStatus, 
    YEAR(podate) as [Year], YEAR(podate) as [poyear] , LEFT (datename(Month,podate),3) as [pomonth]
    From PO_order_hdr
    Where podocstatus IN ('SC','CL','OP')
    ) as POnumber
PIVOT
(
    Count(poyear)
    FOR [pomonth]  IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)

)as PVT

Please help.

Upvotes: 6

Views: 32374

Answers (1)

Dan
Dan

Reputation: 10680

The easiest solution would be to simply do something like this:

Select *,
    Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec AS [Total]
from
    ...

An alternative solution for the general case, would be to use a subselect. Move your inner query into a CTE, to make things a bit easier to work with:

WITH POnumber (POStatus, [Year], [poyear], [pomonth]) AS
(
    Select sase when (podocstatus = 'CL') then 'Closed PO'
         when (podocstatus = 'OP') then 'Open PO'
         when (podocstatus = 'SC') then 'Short Closed PO'   
    end as POStatus, 
    YEAR(podate) as [Year], YEAR(podate) as [poyear] , LEFT (datename(Month,podate),3) as [pomonth]
    From PO_order_hdr
    Where podocstatus IN ('SC','CL','OP')
)
select *,
    -- Subselect that counts the total for the given status and year:
    (select count([Year]) from POnumber T 
     where T.POStatus = PVT.POStatus and T.poyear = PVT.poyear) as [Total]
from POnumber
PIVOT
(
    Count(poyear)
    FOR [pomonth]  IN (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec)
)as PVT

Upvotes: 22

Related Questions