Forum Discussion

ajcbutler2024's avatar
ajcbutler2024
Copper Contributor
Sep 12, 2024

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_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

 

I want to be able to group by Request Display ID so there is only one line per ID and also have a total column at the end but everything I have tried doesn't work.

 

Can anyone help?

 

Thanks

Aaron

 

      • rodgerkong's avatar
        rodgerkong
        Iron Contributor
        Sep 12, 2024

        ajcbutler2024 

        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"

Resources