Forum Discussion
Grouping a SQL Pivot Table and adding a total column
Hi I have created a Pivot table in SQL code: Here is the code: SELECT "Pvt"."Request Display ID" as "Request_Display_ID", "Pvt"."Open" as "Open", "Pvt"."Awaiting User Info" as "Awaiting_U...
Could you supply some sample data?
rodgerkong Here you go. Thanks Rodger
Add a SELECT on outside of pivot query, treat pivot table as subquery, remove total column from pivot. Now you can group it, and add total column. Like this:
SELECT S."Request_Display_ID" ,SUM(S."Open") AS "Open" ,SUM(S."Awaiting_User_Info") AS "Awaiting_User_Info" ,SUM(S."Resolved") AS "Resolved" ,SUM(S."Cancelled") AS "Cancelled" ,SUM(S."On_Hold") AS "On_Hold" ,SUM(S."Closed") AS "Closed" ,SUM(S."Pending_Approval") AS "Pending_Approval" ,SUM(S."Approval_Not_Received") AS "Approval_Not_Received" ,SUM(S."Open" + S."Awaiting_User_Info" + S."Resolved" + S."Cancelled" + S."On_Hold" + S."Closed" + S."Pending_Approval" + S."Approval_Not_Received") AS "Total" FROM ( SELECT "Pvt"."Request Display ID" as "Request_Display_ID", "Pvt"."Open" as "Open", "Pvt"."Awaiting User Info" as "Awaiting_User_Info", "Pvt"."Resolved" as "Resolved", "Pvt"."Cancelled" as "Cancelled", "Pvt"."On Hold" as "On_Hold", "Pvt"."Closed" as "Closed", "Pvt"."Pending Approval" as "Pending_Approval", "Pvt"."Approval Not Received" as "Approval_Not_Received", "Pvt"."Open" + "Pvt"."Awaiting User Info" + "Pvt"."Resolved" + "Pvt"."Cancelled" + "Pvt"."On Hold" + "Pvt"."Closed" + "Pvt"."Pending Approval" + "Pvt"."Approval Not Received" as Total FROM ( SELECT "Request Display ID", "Status", "Timespent in Minutes", "TimeSpent_Hours" FROM "#StatusHistory_PivotData" ) AS SD PIVOT (SUM("SD"."TimeSpent_Hours") FOR "SD"."Status" in ( "Open" , "Awaiting User Info" , "Resolved" , "Cancelled" , "On Hold" , "Closed" , "Pending Approval" , "Approval Not Received" ) ) AS Pvt )AS S GROUP BY S."Request_Display_ID" ORDER BY S."Request_Display_ID"rodgerkong Nearly worked. Would have worked. Looks like the application is blocking it.
See attached.
I'm sorry, my friend, I ran in the wrong direction. The problem is not that complicated.
The original SQL you made is OK basicly, except 2 points:
- The reason that values not group by "Request Display ID" is there is a redundant column in your pivot source, it's "Timespent in Minutes". It prevents pivot from aggregating by "Request Display ID". You can't pivot data from more than one source column.
- The way you calculate total value was right, but you were ignoring the NULL value, caculating with NULL will get NULL, add a ISNULL to every values be calculated will resolve this.
I fixed your original SQL, please check it out:
SELECT "Pvt"."Request Display ID" as "Request_Display_ID", "Pvt"."Open" as "Open", "Pvt"."Awaiting User Info" as "Awaiting_User_Info", "Pvt"."Resolved" as "Resolved", "Pvt"."Cancelled" as "Cancelled", "Pvt"."On Hold" as "On_Hold", "Pvt"."Closed" as "Closed", "Pvt"."Pending Approval" as "Pending_Approval", "Pvt"."Approval Not Received" as "Approval_Not_Received", ISNULL("Pvt"."Open", 0) + ISNULL("Pvt"."Awaiting User Info" , 0) + ISNULL("Pvt"."Resolved" , 0) + ISNULL("Pvt"."Cancelled" , 0) + ISNULL("Pvt"."On Hold", 0) + ISNULL("Pvt"."Closed", 0) + ISNULL("Pvt"."Pending Approval", 0) + ISNULL("Pvt"."Approval Not Received", 0) as "Total" FROM ( SELECT "Request Display ID", "Status", -- SUM("Timespent in Minutes") AS "Timespent in Minutes", --Remove this column "TimeSpent_Hours" FROM "StatusHistory_PivotData" ) AS SD PIVOT ( SUM("SD"."TimeSpent_Hours") FOR "SD"."Status" IN ( "Open" , "Awaiting User Info" , "Resolved" , "Cancelled" , "On Hold" , "Closed" , "Pending Approval" , "Approval Not Received") ) AS Pvt ORDER BY "Pvt"."Request Display ID"
- I think I have cracked the group by by adding Group By "Request Display ID" and then summing the fields in the top section of the query. I just can't get a total for each row. It just comes up as blank.
