SQL alternative to inner join

Question

I have a table which has data that appears as:

Staion  Date        Temperature
A      2015-07-31   8
B      2015-07-31   6
C      2015-07-31   8
A      2003-02-21   4
B      2003-02-21   7
C      2003-02-21   7

For each date I need to create arrays so that it has the following combination:

c1 = (A + B)/2, c2 = (A + B + C)/3 and c3 = (B + C)/2

Right I am doing three different inner join on the table itself and doing a final inner join to achieve the following as result:

Date         c1    c2      c3
2015-07-31   7     7.33    7
2003-02-21   5.5   6       7

Is there a cleaner way to do this?

Answer 1

You can use pivot and calculation on pivoted data as below:

select [Date], c1 = (A+B)/2.0, c2 = (A+B+C)/3.0, C3 = (B+C)/2.0 from 
( select * from #yourstation ) s
pivot (max(temparature) for station in ([A], [B], [C])) p

Your input table:

create table #yourStation (station char(1), date date, Temparature int)

insert into #yourStation (station, date, Temparature) values
 ('A','2015-07-31',   8     )
,('B','2015-07-31',   6     )
,('C','2015-07-31',   8     )
,('A','2003-02-21',   4     )
,('B','2003-02-21',   7     )
,('C','2003-02-21',   7     )