I have a table with following columns
defect_id, developer_name, status, summary, root_cause,
Secondary_RC, description, Comments, environment_name
The column root_cause
has Enviro, Requi, Dev, TSc, TD, Unkn as its values and
column environment_name has QA1, QA2, QA3
I need to prepare a report in the below format
Enviro Requi Dev TSc TD Unkn Total
QA1 9 1 14 17 2 3 46
QA2 8 1 14 0 5 1 29
QA3 1 1 7 0 0 1 10
Total 18 3 35 17 7 5 85
I have prepare the report till
Enviro Requi Dev TSc TD Unkn
QA1 9 1 14 17 2 3
QA2 8 1 14 0 5 1
QA3 1 1 7 0 0 1
I used the below query to get the above result
select *
from
(
select environment_name as " ", value
from test1
unpivot
(
value
for col in (root_cause)
) unp
) src
pivot
(
count(value)
for value in ([Enviro] , [Requi] , [Dev] , [Tsc], [TD] , [Unkn])
) piv
Can anyone help to get the totals for columns and rows?
This T-SQL script contains DDL to create a table and DML to insert a few rows of test data, then a CTE is provided which does some aggregations, while using the Pivot operator to show the data by year and month, along with a new yearly total amount also.
The PIVOT operator allows you to rotate data between columns and rows, performing aggregations along the way. UNPIVOT is the inverse of PIVOT, rotating data from columns to rows.
The aggregate function SUM is ideal for computing the sum of a column's values. This function is used in a SELECT statement and takes the name of the column whose values you want to sum. If you do not specify any other columns in the SELECT statement, then the sum will be calculated for all records in the table.
There may be various approaches to this. You can calculate all the totals after the pivot, or you can get the totals first, then pivot all the results. It is also possible to have kind of middle ground: get one kind of the totals (e.g. the row-wise ones), pivot, then get the other kind, although that might be overdoing it.
The first of the mentioned approaches, getting all the totals after the pivot, could be done in a very straightforward way, and the only thing potentially new to you in the below implementation might be GROUP BY ROLLUP()
:
SELECT
[ ] = ISNULL(environment_name, 'Total'),
[Enviro] = SUM([Enviro]),
[Requi] = SUM([Requi]),
[Dev] = SUM([Dev]),
[Tsc] = SUM([Tsc]),
[TD] = SUM([TD]),
[Unkn] = SUM([Unkn]),
Total = SUM([Enviro] + [Requi] + [Dev] + [Tsc] + [TD] + [Unkn])
FROM (
SELECT environment_name, root_cause
FROM test1
) s
PIVOT (
COUNT(root_cause)
FOR root_cause IN ([Enviro], [Requi], [Dev], [Tsc], [TD], [Unkn])
) p
GROUP BY
ROLLUP(environment_name)
;
Basically, the GROUP BY ROLLUP()
part produces the Total row for you. The grouping is first done by environment_name
, then the grand total row is added.
To do just the opposite, i.e. get the totals prior to pivoting, you could employ GROUP BY CUBE()
like this:
SELECT
[ ] = environment_name,
[Enviro] = ISNULL([Enviro], 0),
[Requi] = ISNULL([Requi] , 0),
[Dev] = ISNULL([Dev] , 0),
[Tsc] = ISNULL([Tsc] , 0),
[TD] = ISNULL([TD] , 0),
[Unkn] = ISNULL([Unkn] , 0),
Total = ISNULL(Total , 0)
FROM (
SELECT
environment_name = ISNULL(environment_name, 'Total'),
root_cause = ISNULL(root_cause, 'Total'),
cnt = COUNT(*)
FROM test1
WHERE root_cause IS NOT NULL
GROUP BY
CUBE(environment_name, root_cause)
) s
PIVOT (
SUM(cnt)
FOR root_cause IN ([Enviro], [Requi], [Dev], [Tsc], [TD], [Unkn], Total)
) p
;
Both methods can be tested and played with at SQL Fiddle:
Method 1
Method 2
Note. I've omitted the unpivoting step in both suggestions because unpivoting a single column seemed clearly redundant. If there's more to it, though, adjusting either of the queries should be easy.
You can find Total for root_cause
and environment_name
using ROLLUP
.
RNO_COLTOTAL
- Logic to place Total
in last column, since the columns Tsc
,Unkn
will overlap the column Total
when pivoting, since its ordering alphabetically.RNO_ROWTOTAL
- Logic to place Total
in last row since a value that is starting with U
,W
,X
,Y
,Z
can overlap the value Total
, since its ordering alphabetically.SUM(VALUE)
- Can define on what aggregate function we can use with ROLLUP
. QUERY 1
SELECT CASE WHEN root_cause IS NULL THEN 1 ELSE 0 END RNO_COLTOTAL,
CASE WHEN environment_name IS NULL THEN 1 ELSE 0 END RNO_ROWTOTAL,
ISNULL(environment_name,'Total')environment_name,
ISNULL(root_cause,'Total')root_cause,
SUM(VALUE) VALUE
INTO #NEWTABLE
FROM
(
-- Find the count for environment_name,root_cause
SELECT DISTINCT *,COUNT(*) OVER(PARTITION BY environment_name,root_cause)VALUE
FROM #TEMP
)TAB
GROUP BY root_cause,environment_name
WITH CUBE
We will get the following logic when CUBE
is used
We declare variables for pivoting.
@cols
- Column values for pivoting.@NulltoZeroCols
- Replace null values with zero.QUERY 2
DECLARE @cols NVARCHAR (MAX)
SELECT @cols = COALESCE (@cols + ',[' + root_cause + ']',
'[' + root_cause + ']')
FROM (SELECT DISTINCT RNO_COLTOTAL,root_cause FROM #NEWTABLE) PV
ORDER BY RNO_COLTOTAL,root_cause
DECLARE @NulltoZeroCols NVARCHAR (MAX)
SET @NullToZeroCols = SUBSTRING((SELECT ',ISNULL(['+root_cause+'],0) AS ['+root_cause+']'
FROM(SELECT DISTINCT RNO_COLTOTAL,root_cause FROM #NEWTABLE GROUP BY RNO_COLTOTAL,root_cause)TAB
ORDER BY RNO_COLTOTAL FOR XML PATH('')),2,8000)
Now pivot it dynamically
DECLARE @query NVARCHAR(MAX)
SET @query = 'SELECT environment_name,'+ @NulltoZeroCols +' FROM
(
SELECT RNO_ROWTOTAL,environment_name,root_cause,VALUE
FROM #NEWTABLE
) x
PIVOT
(
MIN(VALUE)
FOR [root_cause] IN (' + @cols + ')
) p
ORDER BY RNO_ROWTOTAL,environment_name;'
EXEC SP_EXECUTESQL @query
RESULT
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With