Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 642
  • Last Modified:

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
Asked:
LCNW
  • 6
  • 3
1 Solution
 
SharathData 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'

Open in new window

0
 
LCNWAuthor 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
 
SharathData 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

Open in new window

0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LCNWAuthor 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
 
LCNWAuthor Commented:
I worked it out. Thank you! I'll have to look into CTE, haven't done much with that.
0
 
SharathData 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

Open in new window

0
 
LCNWAuthor 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
 
LCNWAuthor Commented:
I've worked it out.

Thank You.
0
 
LCNWAuthor 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]

Open in new window

0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

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