Return column names in one column of a query

[DomTrix]

Hi there, I have a table with 8 integer columns and I want to return the average value for each column in a single query.

I can do it easily enough with 8 columns but I would like the query to contain only two columns, 'columnName' and 'average'.

Is this possible?

Thanks, dom

 

[DomTrix]

DOh, ignore me, I can use COLLATE of course

 

[DomTrix]

I mean UNION!! dumb day

 

[SQLDenis]

Union???

create table test (col1 int,col2 int,col3 int,col4 int)
insert into test
select 1,2,3,4 union all
select 11,2,3,4 union all
select 1,22,3,4 union all
select 1,52,63,4 union all
select 1,2,3,1004

select avg(col1) as col1,avg(col2) as Col2,avg(col3) as col3,avg(col4) as Col4
from test

drop table test

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[DomTrix]

Yah, that would bring out results like:

col1  col2  col3  col4
4.5   2.3   6.7   2.6

I needed results like

colName  average
 'col1'   4.5
 'col2'   2.3
 'col3'   6.7
 'col4'   2.6

Can be achieved with a union:

SELECT avg(col1) as average,
       'col1' as colName

UNION ALL

SELECT avg(col2) as average,
       'col2' as colName

....etc

Thankyou tho

 

[SQLDenis]

I misunderstood, i thought you meant 1 row


Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/