How to have sub total and grandtotal of Pivoted SQL Query (1 Viewer)

[atzdgreat]

TO ACHIEVE:
Dept = total amt of cost center per dept
Div = total amt of department per division
BU = total amt of Div per BU

TABLE:

BU	Div	Dept	CostCenter	Amt
Corporate	Finance	Financial Control	XXFC0101	1,500.00
Corporate	Finance	Financial Control	XXFC0102	1,000.00
Corporate	Finance	Treasury	XXFC0103	100.00
Corporate	HR	HR Admin	XXHR0101	150.00
Corporate	HR	HR Test	XXHR0102	25.00
Corporate	Legal	Legal Admin	XXLC0101	450.00

TO BE DISPLAY:

XXFC0101	XXFC0102	Financial Control	XXFC0103	Treasury	XXHR0101	XXHR0102	HR	Legal Admin	Legal	Corporate
1,500.00	1,000.00	2,500.00	100.00	100.00	150.00	25.00	175.00	450.00	450.00	3,225.00

 

[theDBguy]

A pivot table usually requires a row header. I don't see any in your desired result.

 

[Pat Hartman]

It's a bit unusual to put headers of two different levels at the same level in the result. Also, have you considered how many columns this could end up being?

 

[atzdgreat]

hi @theDBguy and @Pat Hartman. thank you for your reply. header will be the cost center that has been pivot. meaning there's no specific columns for this. depends on user's search. i already got the pivot table of cost centers. knowledge has been learned from other sources. what i want to do now is how can insert new column for sub total for every cost center in every department and another column for division, total for every department.

CODE:

strSQL = "TRANSFORM IIF(SUM(ParCYMAmt) IS NULL, 0, SUM(ParCYMAmt)) AS TotalCost " & _
             "SELECT HGRefNum, HMDetails, HDetails, SHDetails, " & _
             "IIF(SUM(ParCYMAmt) IS NULL, 0, SUM(ParCYMAmt)) AS CYMAmt, IIF(SUM(ParCYYTDAmt) IS NULL, 0, SUM(ParCYYTDAmt)) AS CYYTDAmt, " & _
             "IIF(SUM(ParPYMAmt) IS NULL, 0 , SUM(ParPYMAmt)) AS PYMAmt, IIF(SUM(ParPYYTDAmt) IS NULL, 0, SUM(ParPYYTDAmt)) AS PYYTDAmt FROM ( "


    strSQL = strSQL & _
             "SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
             "SUM(t0.TotCost) AS ParCYMAmt, 0 AS ParCYYTDAmt, 0 AS ParPYMAmt, 0 AS ParPYYTDAmt " & _
             "FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
             "WHERE t0.Period BETWEEN #" & StartDayM & "# AND #" & EndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
             "GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "


    strSQL = strSQL & _
             "UNION ALL " & _
             "SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
             "0 AS ParCYMAmt, SUM(t0.TotCost) AS ParCYYTDAmt, 0 AS ParPYMAmt, 0 AS ParPYYTDAmt " & _
             "FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
             "WHERE t0.Period BETWEEN #" & StartDayYTD & "# AND #" & EndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
             "GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "


    strSQL = strSQL & _
             "UNION ALL " & _
             "SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
             "0 AS ParCYMAmt, 0 AS ParCYYTDAmt, SUM(t0.TotCost) AS ParPYMAmt, 0 AS ParPYYTDAmt " & _
             "FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
             "WHERE t0.Period BETWEEN #" & PYStartDayM & "# AND #" & PYEndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
             "GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "


    strSQL = strSQL & _
             "UNION ALL " & _
             "SELECT t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails, " & _
             "0 AS ParCYMAmt, 0 AS ParCYYTDAmt, 0 AS ParPYMAmt, SUM(t0.TotCost) AS ParPYYTDAmt " & _
             "FROM [Main Table] t0 LEFT JOIN [H Groupings] t1 ON t0.LedgerAcct = t1.LedgerAcct " & _
             "WHERE t0.Period BETWEEN #" & PYStartDayYTD & "# AND #" & PYEndDayM & "# AND t0.CostCenter IN (" & AvCCQuery & ") " & _
             "GROUP BY t1.HGRefNum, t0.LedgerAcct, t0.CostCenter, t1.HMDetails, t1.HDetails, t1.SHDetails "


    strSQL = strSQL & _
             ") x WHERE SHDetails IS NOT NULL GROUP BY HGRefNum, HMDetails, HDetails, SHDetails " & _
             "PIVOT CostCenter"

 

[Pat Hartman]

You need code. There is no way for a query to display both totals and details on the same record. You have to build two separate crosstab queries and then join them. I have no idea short of code how to get the columns into any meaningful order. You probably have to put all the column headers into a table, sort them, then build the final query.

The concept also doesn't make any sense.

 

[atzdgreat]

You need code. There is no way for a query to display both totals and details on the same record. You have to build two separate crosstab queries and then join them. I have no idea short of code how to get the columns into any meaningful order. You probably have to put all the column headers into a table, sort them, then build the final query.

The concept also doesn't make any sense.

thank you @Pat Hartman

 

[theDBguy]

hi @theDBguy and @Pat Hartman. thank you for your reply. header will be the cost center that has been pivot. meaning there's no specific columns for this. depends on user's search. i already got the pivot table of cost centers. knowledge has been learned from other sources. what i want to do now is how can insert new column for sub total for every cost center in every department and another column for division, total for every department.

What you want might be possible in Access, but I wonder if Excel is a better tool for it. Are you able to post a sample db or data?