Hi All.
I have a set of data which looks like below.

I am trying to create a view which would break down the data in a structure similar to the below.

Hi All.
I have a set of data which looks like below.

I am trying to create a view which would break down the data in a structure similar to the below.

SELECT region,
COUNT(CASE system_class WHEN 'dev' THEN 1 END) AS dev,
COUNT(CASE system_class WHEN 'prod' THEN 1 END) AS prod,
COUNT(CASE system_class WHEN 'uat' THEN 1 END) AS uat,
COUNT(*) AS [Grand Total]
FROM tbl
GROUP BY GROUPING SETS((region), ())
ORDER BY grouping(region), region
This is an untested query. Had you posted your sample data as CREATE TABLE + INSERT statements, I would have tested it.
Thanks very much for the proposed answer, is it possible to dynamically generate the system_class ? Instead of specifying it within the select statement, let us assume a new system_class of pre-prod comes into play.
Thanks in advance.
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.
As Melissa suggests, you need to engage in dynamic SQL to handle any set of system classes. Any time you find yourself be looking into to using dynamic SQL, there is all reason to ask yourself if there is an alternative, since dynamic SQL means a higher level of complexity.
The alternative in this case is return a normal relational result set from the database and perform the pivoting in the presentation layer. It is after all a presentational device. For instance, if you want to present in this in Reporting Services, you can use the Tablix report.
But there also situations where the best venue after all for this non-relational operation is in the database, and I have written a short cookbook about it here: http://www.sommarskog.se/dynamic_sql.html#pivot.
9 people are following this question.