Generic grouping and counting enumeration values
-
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; -
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
- 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
-
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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply