Write query to generate statistical analysis example provided

MrFlinstone 646 Reputation points
2021-03-30T21:04:32.02+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,690 questions
{count} votes

4 answers

Sort by: Most helpful
  1. Erland Sommarskog 116.6K Reputation points MVP
    2021-03-30T21:38:21.927+00:00
    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.

    0 comments No comments

  2. MrFlinstone 646 Reputation points
    2021-03-30T22:46:21.587+00:00

    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.

    0 comments No comments

  3. MelissaMa-MSFT 24,211 Reputation points
    2021-03-31T02:10:36.467+00:00

    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.

    0 comments No comments

  4. Erland Sommarskog 116.6K Reputation points MVP
    2021-03-31T11:57:26.453+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.