I have a set of queries that outputs a pivot table. Is it possible to obtain row and/or column subtotals for a pivot table ?
My table I am selecting from looks like this
Site FormID Present
Site 1 Form A Yes
Site 1 Form B Yes
Site 1 Form D Yes
etc...
My pivot table query is this
SELECT *
FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID
FROM @CRFCount WHERE Present='Yes'
GROUP BY Site, FormID) d
PIVOT
(SUM(NumberOfForms)
FOR [Site] IN ([Site 1], [Site 2], [Site 3])
) AS p;
But I really want it to result in this (which of course it does not total for me)
FormID Site 1 Site 2 Site 3 Total
Form A 8 8 15 31
Form B 14 4 NULL 18
Form C 14 NULL NULL 14
Form D 15 3 16 34
Form E 12 4 NULL 16
Form F 14 5 5 24
Form G 14 8 6 28
Form H 22 10 15 47
Form I 15 10 16 41
Form J 15 5 16 36
Total 143 57 89 289
Thanks for your assistance !
-Don
;WITH C as
(
SELECT FormID,
[Site 1],
[Site 2],
[Site 3],
(SELECT SUM(S)
FROM (VALUES([Site 1]),
([Site 2]),
([Site 3])) AS T(S)) as Total
FROM (SELECT Site, COUNT(FormID) AS NumberOfForms,FormID
FROM @CRFCount WHERE Present='Yes'
GROUP BY Site, FormID) d
PIVOT
(SUM(NumberOfForms)
FOR [Site] IN ([Site 1], [Site 2], [Site 3])
) AS p
)
SELECT *
FROM
(
SELECT FormID,
[Site 1],
[Site 2],
[Site 3],
Total
FROM C
UNION ALL
SELECT 'Total',
SUM([Site 1]),
SUM([Site 2]),
SUM([Site 3]),
SUM(Total)
FROM C
) AS T
ORDER BY CASE WHEN FormID = 'Total' THEN 1 END
Note: If you are using SQL Server 2005 you need to change this:
(SELECT SUM(S)
FROM (VALUES([Site 1]),
([Site 2]),
([Site 3])) AS T(S)) as Total
to
(SELECT SUM(S)
FROM (SELECT [Site 1] UNION ALL
SELECT [Site 2] UNION ALL
SELECT [Site 3]) AS T(S)) as Total
Try on SE Data
Try this (not tested):
SELECT *
FROM
(
SELECT
Site = case when grouping(Site)=1 then 'All' else Site end,
FormID = case when grouping(FormID)=1 then 'All' else cast(FormID as varchar(100)) end,
measure = count(NumberOfForms)
FROM @CRFCount
-- chose below
GROUP BY Site, FormID with cube --(ms sql 2005)
--group by grouping sets(Site, FormID, (Site, FormID), ()) --(ms sql 2008)
) AS BOM
PIVOT (max(measure) FOR [Site] IN ([Site 1], [Site 2], [Site 3], [All]))
as pv
SAMPLE TABLE
SELECT * INTO #TEMP
FROM
(
SELECT 'Site 1' [Site], 'Form A' [FormID], 'Yes' Present
UNION ALL
SELECT 'Site 1', 'Form B', 'Yes'
UNION ALL
SELECT 'Site 1', 'Form C', 'Yes'
UNION ALL
SELECT 'Site 1', 'Form B', 'NO'
UNION ALL
SELECT 'Site 1', 'Form C', 'NO'
UNION ALL
SELECT 'Site 2', 'Form A', 'Yes'
UNION ALL
SELECT 'Site 2', 'Form A', 'Yes'
UNION ALL
SELECT 'Site 2', 'Form B', 'Yes'
UNION ALL
SELECT 'Site 2', 'Form B', 'NO'
UNION ALL
SELECT 'Site 2', 'Form C', 'Yes'
UNION ALL
SELECT 'Site 3', 'Form B', 'Yes'
UNION ALL
SELECT 'Site 3', 'Form A', 'Yes'
UNION ALL
SELECT 'Site 3', 'Form C', 'Yes'
UNION ALL
SELECT 'Site 3', 'Form A', 'Yes'
)TAB
1. Row and Column Total
-- Get the columns for dynamic pivot
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']')
FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV
ORDER BY [Site]
-- Since we need Total in last column, we append it at last
SELECT @cols += ',[Total]'
You can use CUBE to get the row and column total on pivoting. More about CUBE here.
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT FORMID,' + @cols + ' FROM
(
SELECT
ISNULL([SITE],''Total'')[SITE],
SUM(CNT)CNT ,
ISNULL(FORMID,''Total'')FORMID
FROM
(
SELECT DISTINCT [SITE],FORMID,
COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT
FROM #TEMP
WHERE PRESENT=''YES''
)TAB
GROUP BY [SITE],FORMID
WITH CUBE
) x
PIVOT
(
MIN(CNT)
FOR [SITE] IN (' + @cols + ')
) p
ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID'
EXEC SP_EXECUTESQL @query
2. Row Total only
You can use ROLLUP to get the row total.
-- Get the columns for dynamic pivot
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']')
FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV
ORDER BY [Site]
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT FORMID,' + @cols + ' FROM
(
SELECT
ISNULL([SITE],''Total'')[SITE],
SUM(CNT)CNT ,
ISNULL(FORMID,''Total'')FORMID
FROM
(
SELECT DISTINCT [SITE],FORMID,
COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT
FROM #TEMP
WHERE PRESENT=''YES''
)TAB
GROUP BY [SITE],FORMID
WITH ROLLUP
) x
PIVOT
(
MIN(CNT)
FOR [SITE] IN (' + @cols + ')
) p
ORDER BY CASE WHEN (FORMID=''Total'') THEN 1 ELSE 0 END,FORMID'
EXEC SP_EXECUTESQL @query
3. Column Total only
Change GROUP BY [SITE],FORMID to GROUP BY FORMID,[SITE]
-- Get the columns for dynamic pivot
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + [Site] + ']', '[' + [Site] + ']')
FROM (SELECT DISTINCT [Site] FROM #TEMP WHERE Present='YES') PV
ORDER BY [Site]
--Since we need Total in last column, we append it at last
SELECT @cols += ',[Total]'
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT FORMID,' + @cols + ' FROM
(
SELECT
ISNULL([SITE],''Total'')[SITE],
SUM(CNT)CNT ,
ISNULL(FORMID,''Total'')FORMID
FROM
(
SELECT DISTINCT [SITE],FORMID,
COUNT(FORMID) OVER(PARTITION BY [SITE],FORMID) CNT
FROM #TEMP
WHERE PRESENT=''YES''
)TAB
GROUP BY FORMID,[SITE]
WITH ROLLUP
) x
PIVOT
(
MIN(CNT)
FOR [SITE] IN (' + @cols + ')
) p
WHERE FORMID <> ''Total''
ORDER BY FORMID'
EXEC SP_EXECUTESQL @query
Now, if you want to replace null with zero, you can use the below code before dynamic pivot.
DECLARE @NulltoZeroCols NVARCHAR (MAX)
SELECT @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+[Site]+'],0) AS ['+[Site]+']'
FROM (SELECT DISTINCT [Site] FROM #TEMP)TAB
ORDER BY [Site] FOR XML PATH('')),2,8000)
SELECT @NullToZeroCols += ',ISNULL([Total],0) AS [Total]'
And in the out most query of dynamic pivot, replace @cols variable with @NullToZeroCols
