CODEWITHSUNDEEP
sqlt-sql
Seen
Seen

Reputation: 4194

Perform Matrix-Like calculation on a master table

I have a master table like

CityName, Month, Temperature

A,Jan,20
....
A,Dec,1
B,Jan,30 ....
Z,December,12

I would like to generate a differentiation table like

iCity,jCity,Month,DifferentTemperature
A, B, Jan, a.temperature-b.temperature
...
A, Z, Dec, a. temperature-z.temperature
.....

each row would loop through the rest row on a particular month in the master table.

What is the best way to do that? How did you come up an idea to solve this problem?

Thank you very much!

Upvotes: 0

Views: 45

Answers (1)

billinkc
billinkc

Reputation: 61249

Sounds like you're looking for a CROSS APPLY Something like

SELECT
    M1.city AS iCity
,   M2.city AS jCity
,   M1.month
,   M1.temperature - M2.temperature AS final_temperature
FROM
    MASTER M1
    CROSS APPLY
    MASTER M2
    ON M2.month = M1.month

Upvotes: 1

Related Questions