• Status: Solved
• Priority: Medium
• Security: Public
• Views: 645

# SQL Totals row with PIVOT

I have the following in a SP:

WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
)
SELECT  p.CrossClampedOn, DATEPART(yyyy,p.CrossClampedOn) AS 'Year',Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted'
;

I would like to add a totals row based on each year. How could I achieve this?
0
LCNW
• 6
• 3
1 Solution

Data EngineerCommented:
You mean this?
``````WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
)
SELECT  p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
,ISNULL(Heart,0)+ISNULL(Intestine,0)+ISNULL(Kidney,0)+ISNULL(Liver,0)+ISNULL(Lung,0)+ISNULL(Pancreas,0) AS [Totals]
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted'
``````
0

Author Commented:
That's a total's column. I need it in a row for each year. So, 5 totals rows. I'm thinking some sort of UNION but not quite sure, yet. This way it fits into my dashboard formatting.

I need it to be laid out as such:

CrossClampedOn                Year    Facility  Hearts ...
2009-10-21 07:13:00.000   2009   FacA      50
2010-10-21 07:13:00.000   2010   FacA      100
2011-10-21 07:13:00.000   2011   FacA      75
2012-10-21 07:13:00.000   2012   FacA      150
2013-10-21 07:13:00.000   2013   FacA      125

2009-10-21 07:13:00.000   2009   FacB      50
2010-10-21 07:13:00.000   2010   FacB      100
2011-10-21 07:13:00.000   2011   FacB      75
2012-10-21 07:13:00.000   2012   FacB      150
2013-10-21 07:13:00.000   2013   FacB      125

2009-10-21 07:13:00.000   2009   Totals    100
2010-10-21 07:13:00.000   2010   Totals    200
2011-10-21 07:13:00.000   2011   Totals    150
2012-10-21 07:13:00.000   2012   Totals    300
2013-10-21 07:13:00.000   2013   Totals    250

I hope this makes sense.
0

Data EngineerCommented:
try this
``````WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')

SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1 GROUP BY CrossClampedOn,[Year]
GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas
``````
0

Author Commented:
That's pulling a syntax error with the final GROUP BY. But that's the idea. Not sure what the error is from.
0

Author Commented:
I worked it out. Thank you! I'll have to look into CTE, haven't done much with that.
0

Data EngineerCommented:
I copy-pasted the GROUP BY twice. try this.
``````WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')

SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
GROUP BY CrossClampedOn,[Year],Heart,Intestine,Kidney,Liver,Lung,Pancreas
``````
0

Author Commented:
This works, but I need it to not prune rows that are null. I'm not sure where to place the ISNULLs for that but it has to have all equal numbers of rows or the dashboard front end doesn't sort properly.
0

Author Commented:
I've worked it out.

Thank You.
0

Author Commented:
Here's the final code. I needed to SUM columns in the final SELECT from CTE.

``````WITH OrgansByTxC AS (
SELECT
p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
dbo.fx_OrgIsExport(ot.TransplantOrgName) AS 'Facility',
dbo.fx_Organ_SimpleName(ot.OrganName) AS 'OrganType',
dbo.fx_Organ_Count(ot.OrganName) AS 'OrganCount',
ot.DispositionOutcomeDesc AS 'Outcome'

FROM
LifeCenterReporting.dbo.rpt_patient p
INNER JOIN
LifeCenterReporting.dbo.rpt_organ_transplant AS ot ON p.PatientId = ot.PatientId
), CTE1 AS (
SELECT  p.CrossClampedOn,
DATEPART(yyyy,p.CrossClampedOn) AS 'Year',
Facility,
ISNULL(Heart,0) AS 'Heart'
,ISNULL(Intestine,0) AS 'Intestine'
,ISNULL(Kidney,0) AS 'Kidney'
,ISNULL(Liver,0) AS 'Liver'
,ISNULL(Lung,0) AS 'Lung'
,ISNULL(Pancreas,0) AS 'Pancreas'
FROM
OrgansByTxC
PIVOT(SUM(OrganCount) FOR OrganType IN (Heart,Intestine,Kidney,Liver,Lung,Pancreas)) P
WHERE Year >= 2009 AND Outcome = 'Transplanted')

SELECT CrossClampedOn,[Year],Facility,Heart,Intestine,Kidney,Liver,Lung,Pancreas
FROM CTE1
UNION ALL
SELECT CrossClampedOn,[Year],'Totals' Facility,SUM(Heart),SUM(Intestine),SUM(Kidney),SUM(Liver),SUM(Lung),SUM(Pancreas)
FROM CTE1
GROUP BY CrossClampedOn,[Year]
``````
0

## Featured Post

• 6
• 3
Tackle projects and never again get stuck behind a technical roadblock.