如何在sqlserver中将列转换为行

I have a table called reviewdb with columns q1,q2,q2.I want to find average of q1,q2 and q3 and store those average values in rows. please help me.

我有一个名为reviewdb的表，列q1，q2，q2。我想找到q1，q2和q3的平均值，并将这些平均值存储在行中。请帮帮我。

q1 q2 q3
5  4  2
4  3  2
4  5  1

find avg of q1,q2, q3 and store avg of q1 in a row and avg of q2 in another row and avg of q3 in the next row.
q  average
q1  4.3
q2  4
q3  1.6

3 个解决方案

#1

One method is a simple union all:

一种方法是简单的联合所有：

select 'q1' as q, avg(q1) as average from t
union all
select 'q2' as q, avg(q2) as average from t
union all
select 'q3' as q, avg(q3) as average from t;

#2

select 'q1' as q, avg(q1) avrg from yourtable

union all  

select 'q2', avg(q2) from yourtable

union all 

select 'q3', avg (q3) from yourtable

#3

Select q 
      ,AVG(CAST(Vals AS DECIMAL(10,2))) Average
from tableName  
  UNPIVOT (vals for q in (q1,q2,q3))up
Group by q

or to get two decimal places

或者得到两位小数

Select q 
      ,CAST(AVG(CAST(Vals AS DECIMAL(10,2)) )AS DECIMAL(10,2)) Average
from @t 
  UNPIVOT (vals for q in (q1,q2,q3))up
Group by q

#1