Perform Matrix-Like calculation on a master table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Perform Matrix-Like calculation on a master table

Author

Topic

seenhzj
Starting Member

8 Posts

Posted - 2011-06-13 : 21:40:01

Hello, 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!

Learning to master T-SQL

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-13 : 21:49:20

[code]
select c1.CityName, c2.CityName, c1.[Month], DifferentTemperature = c1.Temperature - c2.Temperature
from master c1
inner join master c2 on c1.[Month] = c2.[Month]
and c1.CityName < c2.CityName
order by c1.CityName, c2.CityName
[/code]

KH
[spoiler]Time is always against us[/spoiler]

seenhzj
Starting Member

8 Posts

Posted - 2011-06-13 : 22:12:32

quote:

Originally posted by khtan


select	c1.CityName, c2.CityName, c1.[Month], DifferentTemperature = c1.Temperature - c2.Temperature
from	master c1
	inner join master c2	on	c1.[Month]	= c2.[Month]
				and	c1.CityName	< c2.CityName
order by c1.CityName, c2.CityName

KH
[spoiler]Time is always against us[/spoiler]

It looks very good! Thank you very much!

Learning to master T-SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources