Hi @MrFlinstone ,
Based on Erland's query, please refer below dynamic method:
drop table if exists Analysis,#temp
create table Analysis
(srn int identity(1,1),
region varchar(30),
system_class varchar(10),
[owner] varchar(10))
insert into Analysis(region,system_class,[owner]) values
('asia','dev','tom'),
('emea','prod','tom'),
('australia','uat','tom'),
('south america','dev','jack'),
('north america','dev','jack'),
('asia','uat','sam'),
('australia','uat','sam'),
('emea','prod','tayler'),
('north america','prod','tayler'),
('north america','dev','sam')
select IDENTITY(int,1,1) ID,system_class
into #temp
from
(select distinct system_class from Analysis) a
declare @sql nvarchar(max), @s nvarchar(max),@sql1 nvarchar(max)
declare @n int,@max int
set @s=''
set @sql=''
set @sql1=''
set @n=1
select @max=max(id) from #temp
while @n<=@max
begin
select @s='COUNT(CASE system_class WHEN '''+system_class+''' THEN 1 END) AS '+system_class+',' from #temp where id=@n
set @sql=@sql+@s
set @n=@n+1
end
set @sql1=N'SELECT IIF(region is null,''Grand Total'',region) region,'
+@sql+
'COUNT(*) AS [Grand Total]
FROM Analysis
GROUP BY GROUPING SETS((region), ())
ORDER BY grouping(region), region'
EXECUTE sp_executesql @sql1
Output:
region dev prod uat Grand Total
asia 1 0 1 2
australia 0 0 2 2
emea 0 2 0 2
north america 2 1 0 3
south america 1 0 0 1
Grand Total 4 3 3 10
Best regards
Melissa
If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.