Generic grouping and counting enumeration values

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1208

    I have a questionnaire report and the questions is answered with A, B, C or D. My solution is strait forward with a CTE with a bunch of unions. Is there possible to make it more generic? (using column index?)

    -- Test sample for grouping and counting enum values
    -- Table with some questions in differents areas, answer in enum.
    -- Enum : Estimation = (A, B, C, D)

    declare @Estimation table
    (
    Id int not null primary key,
    Clinic nvarchar(20) not null,
    AreaA char(1) null,
    AreaB char(1) null,
    AreaC char(1) null,
    AreaD char(1) null,
    AreaE char(1) null
    -- some more area questions
    );

    insert into @Estimation values(1, N'ClinicZ', N'A', N'A', N'B', N'D', N'C');
    insert into @Estimation values(2, N'ClinicY', N'C', N'D', N'D', N'C', N'A');
    insert into @Estimation values(3, N'ClinicY', N'A', N'D', N'D', N'A', N'B');
    insert into @Estimation values(4, N'ClinicT', N'C', N'A', N'D', N'A', N'C');
    insert into @Estimation values(5, N'ClinicZ', N'C', N'B', N'D', N'A', N'C');
    insert into @Estimation values(6, N'ClinicZ', N'C', N'C', N'B', N'B', N'C');
    insert into @Estimation values(7, N'ClinicT', N'A', N'B', N'D', N'D', N'C');
    insert into @Estimation values(8, N'ClinicY', N'C', N'B', N'A', N'A', N'C');
    insert into @Estimation values(9, N'ClinicX', N'A', N'C', N'B', N'B', N'C');
    insert into @Estimation values(10, N'ClinicX', N'A', N'C', N'A', N'B', N'A');
    insert into @Estimation values(11, N'ClinicY', N'B', N'A', N'A', N'D', N'C');
    insert into @Estimation values(12, N'ClinicX', N'A', N'B', N'A', N'B', N'D');
    insert into @Estimation values(13, N'ClinicY', N'A', N'C', N'B', N'D', N'D');
    insert into @Estimation values(14, N'ClinicZ', N'D', N'C', N'B', N'C', N'D');
    insert into @Estimation values(15, N'ClinicT', N'B', N'C', N'D', N'A', N'D');
    insert into @Estimation values(16, N'ClinicZ', N'C', N'C', N'D', N'C', N'B');
    insert into @Estimation values(17, N'ClinicZ', N'A', N'A', N'B', N'C', N'C');
    insert into @Estimation values(18, N'ClinicT', N'C', N'C', N'C', N'A', N'B');
    insert into @Estimation values(19, N'ClinicT', N'C', N'C', N'D', N'C', N'D');
    insert into @Estimation values(20, N'ClinicT', N'A', N'B', N'B', N'C', N'A');

    -- Result
    -- |Clinic |Area |A|B|C|D|
    -- |ClinicT|AreaA|2|1|3|0|
    -- |ClinicT|AreaB|1|2|3|0|
    -- |ClinicT|AreaC|0|1|1|4|
    -- |ClinicT|AreaD|3|0|2|1|
    -- |ClinicT|AreaE|1|1|2|2|
    -- |ClinicX|AreaA|||||
    -- |ClinicX|...|||||
    -- |ClinicY|...
    -- |ClinicZ|...

    -- CTE with unions
    with cte (Clinic, Area, A, B, C, D)
    as
    (
    select e.Clinic,
    'AreaA' as Area,
    case when e.AreaA = 'A' then 1 else 0 end as A,
    case when e.AreaA = 'B' then 1 else 0 end as B,
    case when e.AreaA = 'C' then 1 else 0 end as C,
    case when e.AreaA = 'D' then 1 else 0 end as D
    from @Estimation as e
    union all
    select e.Clinic,
    'AreaB' as Area,
    case when e.AreaB = 'A' then 1 else 0 end as A,
    case when e.AreaB = 'B' then 1 else 0 end as B,
    case when e.AreaB = 'C' then 1 else 0 end as C,
    case when e.AreaB = 'D' then 1 else 0 end as D
    from @Estimation as e
    union all
    select e.Clinic,
    'AreaC' as Area,
    case when e.AreaC = 'A' then 1 else 0 end as A,
    case when e.AreaC = 'B' then 1 else 0 end as B,
    case when e.AreaC = 'C' then 1 else 0 end as C,
    case when e.AreaC = 'D' then 1 else 0 end as D
    from @Estimation as e
    union all
    select e.Clinic,
    'AreaD' as Area,
    case when e.AreaD = 'A' then 1 else 0 end as A,
    case when e.AreaD = 'B' then 1 else 0 end as B,
    case when e.AreaD = 'C' then 1 else 0 end as C,
    case when e.AreaD = 'D' then 1 else 0 end as D
    from @Estimation as e
    union all
    select e.Clinic,
    'AreaE' as Area,
    case when e.AreaE = 'A' then 1 else 0 end as A,
    case when e.AreaE = 'B' then 1 else 0 end as B,
    case when e.AreaE = 'C' then 1 else 0 end as C,
    case when e.AreaE = 'D' then 1 else 0 end as D
    from @Estimation as e
    )
    select e.Clinic,
    e.Area,
    sum(e.A) as A,
    sum(e.B) as B,
    sum(e.C) as C,
    sum(e.D) as D
    from cte as e
    group by e.Clinic, e.Area
    order by e.Clinic, e.Area;
  • Mark Cowne

    One Orange Chip

    Points: 26743

    Here's a simpler alternative to your query

    select e.Clinic,ca.Area,
    sum(case when ca.AreaVal = 'A' then 1 else 0 end) as A,
    sum(case when ca.AreaVal = 'B' then 1 else 0 end) as B,
    sum(case when ca.AreaVal = 'C' then 1 else 0 end) as C,
    sum(case when ca.AreaVal = 'D' then 1 else 0 end) as D
    from @Estimation e
    cross apply (values('AreaA',AreaA),('AreaB',AreaB),('AreaC',AreaC),('AreaD',AreaD),('AreaE',AreaE)) ca(Area,AreaVal)
    group by e.Clinic,ca.Area
    order by e.Clinic,ca.Area;

    If you want this to be more generic, have a look here for some pointers

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

     

     

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537
  • scdecade

    Say Hey Kid

    Points: 693

    Mark Cowne wrote:

    Here's a simpler alternative to your query

    select e.Clinic,ca.Area,
    sum(case when ca.AreaVal = 'A' then 1 else 0 end) as A,
    sum(case when ca.AreaVal = 'B' then 1 else 0 end) as B,
    sum(case when ca.AreaVal = 'C' then 1 else 0 end) as C,
    sum(case when ca.AreaVal = 'D' then 1 else 0 end) as D
    from @Estimation e
    cross apply (values('AreaA',AreaA),('AreaB',AreaB),('AreaC',AreaC),('AreaD',AreaD),('AreaE',AreaE)) ca(Area,AreaVal)
    group by e.Clinic,ca.Area
    order by e.Clinic,ca.Area;

    If you want this to be more generic, have a look here for some pointers

    https://www.sqlservercentral.com/articles/cross-tabs-and-pivots-part-2-dynamic-cross-tabs

    The version is 2019 so the OP could use IIF 🙂

    select e.Clinic,ca.Area,
    sum(iif(ca.AreaVal='A', 1, 0)) A, sum(iif(ca.AreaVal='B', 1, 0)) B,
    sum(iif(ca.AreaVal='C', 1, 0)) C, sum(iif(ca.AreaVal='D', 1, 0)) D
    from @Estimation e
    cross apply (values('AreaA',AreaA),('AreaB',AreaB),('AreaC',AreaC),
    ('AreaD',AreaD),('AreaE',AreaE)) ca(Area,AreaVal)
    group by e.Clinic,ca.Area
    order by e.Clinic,ca.Area;

    Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können

  • jonas.gunnarsson 52434

    Ten Centuries

    Points: 1208

    Thanks, cross apply, makes it simpler, and easy to extend.

    Edit: Missed ca.Area. IIF seems nice, need to try.

    Thanks 🙂

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply