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;
February 28, 2020 at 10:31 am
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/61537Here'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
February 28, 2020 at 2:31 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy