PIVOT Function in SQL Statement 1

[fcaldero]

Hello -
Have a SQL where a PIVOT function creates the following:

test_number SHIFT1 SHIFT2 SHIFT3
9000 4 3 0
9002 10 8 0
9008 2 2 0
9010 2 0 0

I would like to add a TOTAL colum and Row where I get totals by SHIFT and by Test, therefore test 9000 would have a total of 7 and SHIFT1 would have a total of 18. Is this possible? Moreover, eventually I would like to calculate % of test-totals - the calculation would be equal to: total number by test_number divided by total number for all tests (i.e. for test number 9000 the value would be 7/31 = 22.58%. So the table would look like this:

test_number SHIFT1 SHIFT2 SHIFT3 TOTAL % of Test-Totals
9000 4 3 0 7 22.58%
9002 10 8 0 18 58.06%
9008 2 2 0 4 12.90%
9010 2 0 0 2 6.45%
TOTAL 18 13 0 31 100.00%

THANK YOU FOR ANY ASSISTANCE YOU MAY BE ABLE TO GIVE

 

[markros]

Assuming the original result is in the table or CTE, you can do

;with cte as (Original Pivot Query),
cte1 as (select *, Shift1 + Shift2 + Shift3 as Total
from CTE)
cte2 as (
select 'TOTAL' as Test_Number, SUM(Shift1) as Shift1,
SUM(Shift2) as Shift2, SUM(Shift3) as Shift3,
SUM(Shift1 + Shift2 + Shift3) as Total)

select cte1.*, cast(cte1.Total / NULLIF(cte2.Total,0) as decimal(12,2)) * 100 as [% of Test-Totals]
from cte1, cte2 -- cross join, but since there is 1 record in cte2 it should work
UNION ALL
select cte2.*, 100

From the top of my head.


PluralSight Learning Library

 

[fcaldero]

Thanks markros. Your approach helped get the table I needed. I ended doing a view and from the view used your logic to get the %of Test-Totals. Good suggestions.