question

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 asked MelissaMa-msft commented

Write query to generate statistical analysis example provided

Hi All.

I have a set of data which looks like below.

82992-image.png


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

83002-image.png


sql-server-transact-sql
image.png (21.9 KiB)
image.png (13.3 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @MrFlinstone-1451,

Could you please validate and provide any update? Thanks.

Best regards
Melissa

0 Votes 0 ·
ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered
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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MrFlinstone-1451 avatar image
0 Votes"
MrFlinstone-1451 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @MrFlinstone-1451,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.