Find Out Average By Group Wise And After That Convert Column To Row

I have a table 'tbTest' like this: id | q1 | q2 | q3 | type ---+----+----+----+--------- 1 | 1 | 2 | 3 | student 2 | 2 | 1 | 3 | student 3 | 1 | 3 | 2 | student 4 | 3

Solution 1:

Based on the C# tag, I am going to assume you are using SQL Server. My recommendation is to unpivot the data using apply and then reaggregate:

select v.q,
       avg(casewhen t.type ='student'then v.val *1.0end) as student,
       avg(casewhen t.type ='alumni'then v.val *1.0end) as alumni
from tbTest t cross apply
     (values ('q1', t.q1), ('q2', t.q2), ('q3', t.q3)) v(q, val)
groupby v.q;

Similar logic can be implemented in other databases either using union all or an explicit union all.

Here is a db<>fiddle.

Solution 2:

The operation you want is commonly called PIVOT in different RDBMS, but it's not standard SQL and not supported by every RDBMS.

If you don't have many groups, then you can instead hardcode your query for each group (just like @Dusan suggested)

SELECT'q1'as q
  (SELECTAVG(q1) WHERE type='student') as student,
  (SELECTAVG(q1) WHERE type='alumni') as alumni
FROM tbTest
UNIONALLSELECT'q2'
  (SELECTAVG(q2) WHERE type='student'),
  (SELECTAVG(q2) WHERE type='alumni')
FROM tbTest
UNIONALLSELECT'q3'
  (SELECTAVG(q3) WHERE type='student'),
  (SELECTAVG(q3) WHERE type='alumni')
FROM tbTest