Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
Solved

# SQL Totals row with PIVOT

Posted on 2013-11-22
Medium Priority
630 Views
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
Question by:LCNW
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 6
• 3

LVL 41

Expert Comment

ID: 39670683
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

LVL 1

Author Comment

ID: 39670701
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

LVL 41

Expert Comment

ID: 39670709
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

LVL 1

Author Comment

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

LVL 1

Author Comment

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

LVL 41

Accepted Solution

Sharath earned 2000 total points
ID: 39670744
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

LVL 1

Author Comment

ID: 39670780
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

LVL 1

Author Comment

ID: 39670880
I've worked it out.

Thank You.
0

LVL 1

Author Comment

ID: 39681664
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be \$37.1B.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a paâ€¦
Viewers will learn how the fundamental information of how to create a table.
###### Suggested Courses
Course of the Month5 days, 4 hours left to enroll