如何在sqlserver中的行中存储列值 [英] how store column values in rows in sqlserver

问题描述

我想在每列中找到平均值，然后我想在行中存储平均值。怎么做？

类似于

 col1 col2 col3 
 3 4 5 
 5 5 6 

如你所见

 col1的平均值是4 
 col2的平均值是4.5 
 col3的平均值是5.5 

现在我希望平均值存储在行中

 avg1 4 
 avg2 4.5 
 avg3 5.5 

解决方案

< blockquote>学习使用 PIVOT和UNPIVOT [ ^ ]。

另一种选择是使用 UNION ALL [ ^ ]。

  SELECT  ColName，AvgValue 
  FROM （
  SELECT  '  Avg1'  AS  ColName，AVG（Col1） As  AvgValue 
  FROM  TableName 
  UNION   ALL  
  SELECT    Avg2'  AS  ColName，AVG（Col2） As  AvgValue 
  FROM  TableName 
  UNION   ALL  
  SELECT  '  Avg3'  AS  ColN ame，AVG（Col3） As  AvgValue 
  FROM  TableName 
） AS  T 

测试示例： SqlFiddle [ ^ ]

使用sql server的PIVOT功能，简单易用

I want to find average of values in each column and then i want to store average values in rows. how to do it?
something like

col1 col2 col3
3 4 5
5 5 6

As you can see

average of col1 is 4
average of col2 is 4.5
average of col3 is 5.5

Now i want the average values to be stored in rows

avg1 4
avg2 4.5
avg3 5.5

解决方案

Learn to use PIVOT and UNPIVOT[^].

Another option is to use UNION ALL[^].

SELECT ColName, AvgValue
FROM (
    SELECT 'Avg1' AS ColName, AVG(Col1) As AvgValue
    FROM TableName
    UNION ALL
    SELECT 'Avg2' AS ColName, AVG(Col2) As AvgValue
    FROM TableName
    UNION ALL
    SELECT 'Avg3' AS ColName, AVG(Col3) As AvgValue
    FROM TableName
) AS T

Tested example: SqlFiddle[^]

use PIVOT functionality of sql server, it is easy and simple

这篇关于如何在sqlserver中的行中存储列值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！