user1554650 user1554650 - 5 months ago 8
SQL Question

SQL Server query hierarchical results using a CTE

I have an

emp
table like this:

enter image description here

I need to group by
DeptNo
and
JOB
.

I am using the following code to generate group by

;With Cte as
(
select
DeptNo, Count(EMPNO) Total
from
Emp
group by
DeptNo

union all

select
DeptNo, Count(EMPNO) Total
from
Emp
group by
DeptNo, Job
)
select *
from Cte
order by DeptNo, Total desc


But I need to make the output as below

enter image description here

Is there any option to generate similar results in SQL with dynamic CTE?

Thanks

Answer
SELECT x.DeptNo AS [Dept/Job], COUNT(*) AS Cnt, x.DeptNo, 1 AS RowType
FROM dbo.emp x
GROUP BY x.DepNo

UNION ALL 

SELECT y.Job, COUNT(*), y.DepNo, 2 AS RowType
FROM dbo.emp y
GROUP BY y.Job, y.DepNo

ORDER BY DepNo, RowType
Comments