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.
You can find Total for root_cause and environment_name using ROLLUP . RNO_COLTOTAL - Logic to place Total in last column, since the columns Tsc , Unkn will overlap the column Total when pivoting, since its ordering alphabetically.
In order to calculate a subtotal in SQL query, we can use the ROLLUP extension of the GROUP BY statement. The ROLLUP extension allows us to generate hierarchical subtotal rows according to its input columns and it also adds a grand total row to the result set.
The SUM() function returns the total sum of a numeric column.
Here's one way to do it: You can use INFORMATION_SCHEMA. COLUMNS View to get all the column names and put them in a temp table. Next, create a temp table to store your sums. You can then use dynamic sql and a loop to sum each column.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With