Summary table with row/column totals using SQL pivot

Question

I need help with SQL for generating pivot table with row/column totals calculated ..I have 2 tables as given below

Table ProbCat
==============
probcat | probdesc
1         minor
2         high
3         showstopper


Table ProbSummary
===================
prodcat | noofproblems | stage
1          5             Dev 
2          1             Dev 
3          6             QA
3          6             Prod

and I would like to generate a pivot table with row/column total percentages as shown below. I have tried combination of 'pivot' and 'group by' but could not get the row & column total accurately

Probelm Summary view:

ProbCategory  CategoryDesc  Dev   Qa   Prod    Total(%)
______________________________________________________
1             Minor          5     0    0       5(100*(5/18))
2             High           1     0    0       1(100*(1/18))
3             Showstopper    0     6    6       12(100*(6/18))
Total         NA             6(%)  6(%) 6(%)

the row with the total should be left to the presentation layer. Not sql-server. — mxix, Jul 14 '15 at 20:43
I agree....I've been playing around with this and to achieve what you want is massively overcomplicated compared to how easy it would be to calculate row and column totals in the presentation layer. — Mat Richardson, Jul 14 '15 at 20:54
Thanks for the feedback, I was able to achieve it with mix of query and presentation layer for calculating column total percentages — Thirumala Kalluri, Jul 14 '15 at 22:59

score 1 · Accepted Answer · answered Jul 14 '15 at 21:54

Just like others mentioned, your summary/total calculation should be done on the presentation layer. But here is my attempt to getting your output minus the last summary line:

;WITH Q1
AS (
    SELECT pvt.probcat
        ,pvt.probdesc
        ,ISNULL(pvt.[Dev], 0) AS 'Dev'
        ,ISNULL(pvt.[QA], 0) AS 'QA'
        ,ISNULL(pvt.[Prod], 0) AS 'Prod'
    FROM (
        SELECT pc.probcat
            ,pc.probdesc
            ,ps.noofproblems
            ,ps.stage
        FROM Probcat pc
        LEFT JOIN ProbSummary ps ON pc.probcat = ps.probcat
        ) t
    PIVOT(max(noofproblems) FOR stage IN (
                [Dev]
                ,[QA]
                ,[Prod]
                )) pvt
    ),
q2 as
(SELECT q1.*
    ,sum(q1.Dev + q1.QA + q1.Prod) AS Total
FROM q1

GROUP BY q1.probcat
    ,q1.probdesc
    ,q1.Dev
    ,q1.QA
    ,q1.Prod
)
select q2.probcat
    ,q2.probdesc
    ,q2.Dev
    ,q2.QA
    ,q2.Prod
    ,cast(q2.Total as varchar(10)) + ' (' +
    cast(cast((cast(q2.Total as decimal(5,2))/cast(d.CrossSum as decimal(5,2)))*100 
    as decimal(5,2)) as varchar(10))
    + '% )' as FinalTotal
    from q2
  CROSS APPLY (
    SELECT sum(q1.Dev + q1.QA + q1.Prod) AS CrossSum
    FROM q1
    ) d  
ORDER BY q2.probcat

SQL Fiddle Demo

Vamsi Prabhala · Answer 2 · 2015-07-14T21:01:41.503

0

with x as
(select p.probcat as probcategory, p.probdesc as categotydesc,
case when s.stage = 'Dev' and s.noofproblems > 0 then s.noofproblems else 0 end as Dev,
case when s.stage = 'QA' and s.noofproblems > 0 then s.noofproblems else 0 end as QA,
case when s.stage = 'Prod' and s.noofproblems > 0 then s.noofproblems else 0 end as Prod
from Probcat p join Probsummary s on p.probcat = s.prodcat)
select probcategory,categotydesc,Dev,QA,Prod, Dev+QA+Prod as Total
from x

this should give what you need except the Total row at the bottom.

edited Jul 14 '15 at 21:01

answered Jul 14 '15 at 20:55

Vamsi Prabhala

48,685
4
36
58

Thanks for providing the query , I was able to resolve with all responses – Thirumala Kalluri Jul 14 '15 at 22:53

score 0 · Answer 3 · answered Jul 14 '15 at 21:28

Here's a PIVOT query you can work with.. your example has prodcat in one table and probcat in the other.. not sure if that was typo in second table so i just with with probcat for both. should be easy to fix it it gives you an error

;
WITH ProbCatSummary AS
(
    SELECT  pc.probcat,
            pc.probdesc,
            SUM(ps.noofproblems) AS noofproblems
    FROM    ProbCat pc 
            JOIN ProbSummary ps ON pc.probcat = ps.probcat
    GROUP BY pc.probcat,
            pc.probdesc
)
SELECT  p.*,  
        [Total (%)] =   CONVERT(VARCHAR,[Dev] + [QA] + [Prod]) 
                        + ' (' + CONVERT(VARCHAR,  
                                    CAST(ROUND((([Dev] + [QA] + [Prod]) * 100.0 / SUM(ps.noofproblems) OVER()),2) AS DECIMAL(8,2))
                                ) 
                        + ')'
FROM (
    SELECT  probcat, probdesc, ISNULL([Dev],0)[Dev], ISNULL([QA],0)[QA], ISNULL([Prod],0)[Prod]
    FROM    (SELECT pc.probcat, probdesc, stage, noofproblems
            FROM    ProbCat pc 
                    JOIN ProbSummary ps ON pc.probcat = ps.probcat) AS s
    PIVOT (SUM(noofproblems) FOR stage IN ([Dev], [QA], [Prod])) AS p
) p JOIN ProbCatSummary ps ON p.probcat = ps.probcat

Thanks for providing the query , I was able to resolve with all responses — Thirumala Kalluri, Jul 14 '15 at 22:53

Summary table with row/column totals using SQL pivot

Probelm Summary view:

3 Answers3