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

No need for a JOIN, you could simply use a GROUP BY and an aggregation function:

WITH CTE AS
(
    SELECT  [Date],
            MIN(CASE WHEN Staion = 'A' THEN Temperature END) A,
            MIN(CASE WHEN Staion = 'B' THEN Temperature END) B,
            MIN(CASE WHEN Staion = 'C' THEN Temperature END) C
    FROM dbo.YourTable  
    GROUP BY [date]
)
SELECT  [Date],
        (A+B)/2 c1,
        (A+B+C)/3 c2,
        (B+C)/2 c3
FROM CTE;