how store column values in rows in sqlserver

Question

0.00/5 (No votes)

See more:

SQL-Server-2012

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

Posted 24-Nov-15 5:04am

Member 8499677

Updated 24-Nov-15 8:43am

Maciej Los

v2

Add a Solution

Comments

Maciej Los 24-Nov-15 14:45pm

Based on what condition?

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Peter Leow · Answer 1 · 2015-11-24T05:22:00

Solution 1

Learn to use PIVOT and UNPIVOT[^].

Posted 24-Nov-15 5:22am

Peter Leow

Updated 24-Nov-15 5:32am

v2

Comments

Maciej Los 24-Nov-15 14:46pm

5ed!

Maciej Los · Answer 2 · 2015-11-24T08:54:00

Solution 2

Another option is to use UNION ALL[^].

SQL

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[^]

Posted 24-Nov-15 8:54am

Maciej Los

Updated 24-Nov-15 9:03am

v2

Comments

Peter Leow 24-Nov-15 21:04pm

Brilliant! 5ed.

Maciej Los 25-Nov-15 2:14am

Thank you, Peter.
BTW: i do not see your 5 ;(

Member 12150801 · Answer 3 · 2015-11-24T11:01:00

Solution 3

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

Posted 24-Nov-15 11:01am

Member 12150801

Comments

Maciej Los 25-Nov-15 2:15am

This was mentioned in soultion 1...