Best way to aggregate data in this SQL table?

Question

I'm building a .NET 6 application that uses some EF. Let's say I have a stored procedure that returns the following table:

 declare @t as table (r1 varchar(20), r2 varchar(20), r3 varchar(20), col varchar(2))

insert into @t select 'Strongly Agree', 'Agree', 'Strongly Agree', 'r2'
insert into @t select 'Strongly Disagree', 'Agree', 'Strongly Agree', 'r2'
insert into @t select 'Agree', 'Agree', 'Strongly Agree', 'r3'
insert into @t select 'Disagree', 'Agree', 'Strongly Agree', 'r3'
insert into @t select 'Strongly Agree', 'Agree', 'Agree', 'r3'

select * from @t

I'd like to aggregate this data and display it as:

r1
Strongly Agree - 2 (40%)
Agree - 1 (20%)
Disagree - 1 (20%)
Strongly Disagree - 1 (20%)

**r2 / r3* **
Strongly Agree - 2 (40%)
Agree - 3 (60%)

• Note that for r2 / r3, the value in the last column of @t (col) determines which column the data should be pulled from. For example, a value of 'r2' should retrieve the data in column r2 and disregard column r3, and vice versa.

Any tips / recommendations on a good way to go about this?

Answer 1

Hi @Brian ,

Welcome to the microsoft TSQL Q&A forum!

Your question is related to tsql, so my colleague helped you add the tsql tag so that you can get more professional help.

Please check:

declare @t as table (r1 varchar(20), r2 varchar(20), r3 varchar(20), col varchar(2))  
      
insert into @t select 'Strongly Agree', 'Agree', 'Strongly Agree', 'r2'  
insert into @t select 'Strongly Disagree', 'Agree', 'Strongly Agree', 'r2'  
insert into @t select 'Agree', 'Agree', 'Strongly Agree', 'r3'  
insert into @t select 'Disagree', 'Agree', 'Strongly Agree', 'r3'  
insert into @t select 'Strongly Agree', 'Agree', 'Agree', 'r3'  
      
;with cte as(  
select *,case when col='r2' then r2 else r3 end [r2-r3]   
from  @t)  
   
select distinct r1+ ' - '+cast(count(r1) over(partition by r1) as varchar(10))   
+' (' +cast(count(r1) over(partition by r1)*100/count(r1)   
over(order by (select 1)) as varchar(10))+ '%'+')' as r1   
from cte  
union all  
select 'r2 / r3*'  
union all  
select distinct [r2-r3]+ ' - '+cast(count([r2-r3]) over(partition by [r2-r3]) as varchar(10))   
+' (' +cast(count([r2-r3]) over(partition by [r2-r3])*100/count([r2-r3])   
over(order by (select 1)) as varchar(10))+ '%'+')' [r2/r3]  
from cte  

Output:

If you have any question, please feel free to let me know.
If the response is helpful, please click "Accept Answer" and upvote it.

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

The table is not normalized which makes queries very complex to write.

 DECLARE @t as table (
  Id INT PRIMARY KEY IDENTITY(1,1) NOT NULL,
  QuestionId INT NOT NULL,
  Answer VARCHAR(20)
)
 INSERT INTO @t 
 VALUES (1, 'Strongly Agree'),
 (1, 'Strongly Agree'),
 (1, 'Agree'),
 (1, 'Disagree'),
 (1, 'Strongly Disagree'),
 (2, 'Strongly Agree'),
 (2, 'Strongly Agree'),
 (2, 'Agree'),
 (2, 'Agree'),
 (3, 'Strongly Agree'),
 (3, 'Strongly Agree'),
 (3, 'Agree'),
 (3, 'Agree')


 SELECT QuestionId, Answer, COUNT(Answer) AS AnswerCount
 FROM @t
 GROUP BY  QuestionId, Answer
 ORDER BY QuestionId, COUNT(Answer) desc


QuestionId  Answer               AnswerCount
----------- -------------------- -----------
1           Strongly Agree       2
1           Agree                1
1           Disagree             1
1           Strongly Disagree    1
2           Agree                2
2           Strongly Agree       2
3           Strongly Agree       2
3           Agree                2

Otherwise; you're forced to add steps to get the result set into a format that can be aggregated. I believe it's easy to fix the table design.

SELECT t.QuestionId, t.Answer, COUNT(Answer) AS AnswerCount
FROM
(   
 select r1 as 'Answer', 'r1' as 'QuestionId'  from @t
 UNION ALL
 select r2 as 'Answer', 'r2/r3' as 'QuestionId' from @t WHERE col = 'r2'
 UNION ALL
 select r3 as 'Answer', 'r2/r3' as 'QuestionId' from @t WHERE col = 'r3'
) as t
GROUP BY QuestionId, Answer
ORDER BY QuestionId, COUNT(Answer) desc



QuestionId Answer               AnswerCount
---------- -------------------- -----------
r1         Strongly Agree       2
r1         Agree                1
r1         Disagree             1
r1         Strongly Disagree    1
r2/r3      Agree                3
r2/r3      Strongly Agree       2