Grouping a SQL Pivot Table and adding a total column

ajcbutler2024 

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

ajcbutler2024 

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:

1. 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.
2. 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"