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...
rodgerkong Nearly worked. Would have worked. Looks like the application is blocking it.
See attached.
This application is weird……
Try to use CTE. pivot in CTE body，grouping and calculating total are out there.
Code is here
WITH S
AS
(
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
)
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
S
GROUP BY
S.Request_Display_ID
ORDER BY
S.Request_Display_ID
