Cross row query / join / arithmetic

Question

Post reply

Cross row query / join / arithmetic

JB-458262

Valued Member

Points: 63
More actions
June 28, 2007 at 12:34 pm

#120447

Hi.
I have a table similar to this:
Names:
Index | Name | Year | Number1 | Number2
1 | Jon | 2001 | 145.30 | 173.20
2 | Jon | 2002 | 150.30 | 139.11
3 | Jon | 2003 | 112.00 | 560.00
4 | Jon | 2004 | 145.34 | 890.00
5 | Bob | 2001 | 199.30 | 1001.78
6 | Bob | 2002 | 250.30 | 123.15
7 | Bob | 2003 | 189.30 | 1034.11
8 | Bob | 2004 | 173.30 | 450.10
I would like to merge the rows into a format similar to this:
Index | Name | Year | Number1 | Number2
1 | Jon | 2001/2002 | 295.60 | 312.31
2 | Jon | 2002/2003 | 262.30 | 699.11
3 | Jon | 2003/2004 | 157.34 | 1450
Bob...
I want the numbers for the year column and numbers to be added together. Can anyone suggest a way to do this? I don't want to use any temporary tables or views. I am coding this for SQL 7 and 2000.
I am really stuck. Thanks so much for your help.
Jon

Viewing 8 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

mrpolecat SSCertifiable Points: 7014 More actions · Answer 1

How do you identify which rows you want to add together? Is it always the next year? Are the years int or varchar?

JB-458262 Valued Member Points: 63 More actions · Answer 2

The year column is varchar. I identify the rows by the name (Jon) and then by the year (2001, 2002) etc.

mrpolecat SSCertifiable Points: 7014 More actions · Answer 3

so it is always the first year joined with the year after. Are there gaps in the years and if so do they need to be accounted for so if 2002 does not exist 2001 and 2003 need to be joined?

mrpolecat SSCertifiable Points: 7014 More actions · Answer 4

select v1.[name],v1.[year]+','+v2.[year],v1.number1+v2.number1,v1.number2+v2.number2

from

(select [name],[year],number1,number2 from t1) v1

join

(select [name],[year],number1,number2 from t1) v2

on v1.[name]=v2.[name] and convert(int,v1.year) = convert(int,v2.year)+1

noeld SSC Guru Points: 96590 More actions · Answer 5

no need for inline views just use directly the table:

select v1.[name],v1.[year]+'/'+v2.[year] as period ,v1.number1+v2.number1,v1.number2+v2.number2

from t1 v1

join t1 v2

on v1.[name]=v2.[name] and convert(int,v1.year) = convert(int,v2.year)+1

* Noel

JB-458262 Valued Member Points: 63 More actions · Answer 6

Superb! Thank you *so* much!!

It seems so much easier than I first imagined...

mrpolecat SSCertifiable Points: 7014 More actions · Answer 7

You are right Noel, I had started down one path and didn't see the forest through the trees.

JB, I'm not sure if those are your actual field names but using names like index,name, and year isn't a good idea. They are all reserved words in SQL and make the query more confusing.