| Author |
Topic |
|
petit-pere
Starting Member
12 Posts |
 Posted - 2008-03-12 : 05:32:37
|
| Hi everybodyI am using SQL server 2000 and am puzzled with a problem ... I hope somebody can help me out of this maze.A query returns those results:ID1 ID2 type amount101 1 10 n1101 2 10 n2101 3 10 n3101 1 20 n4101 1 20 n5101 1 20 n6I'd like to join these rows to return the following result:ID1 ID2 type amount1 amount2101 1 10 n1 n4101 2 10 n2 n5101 3 10 n3 n6I will appreciate any help on this problem ...:-) |
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-03-12 : 05:35:52
|
| i don't understand. what are your rules here? why should n5 be joined to n2? or n6 to n3?Em |
 |
|
|
petit-pere
Starting Member
12 Posts |
Posted - 2008-03-12 : 05:45:19
|
| they are in sequence ... records with type 10 are sorted by ID2they come in sequence for type 20a type 10 matches with a type 20; if type 10 and ID2 = 1 then the first record with type 20 matches, if ID2 = 2, then the second with type 20 matches, a.s.o. ...I hope this makes sense :) |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-03-12 : 05:52:27
|
| that's really horrible! is there no other columns in your table? nothing else to tell you the 'order' of this sequence?Em |
|
|
|
petit-pere
Starting Member
12 Posts |
Posted - 2008-03-12 : 06:03:44
|
| that's horrible, i agreeno, no other column to join the rows, i'm afraid ... i'm already working 2 days on this and that's the only way i found out. i'm trying to write the query with a cursor now, but if you have a solution without one, i'll go for it:) |
|
|
|
elancaster
A very urgent SQL Yakette
1208 Posts |
Posted - 2008-03-12 : 06:11:03
|
| there's no primary key? the problem is whilst you say it's the order they appear, there isn't really an order at all without a clustered index on that table. can the n4, n5, n6 be taken as an implicit order?Em |
|
|
|
petit-pere
Starting Member
12 Posts |
Posted - 2008-03-12 : 06:33:25
|
| oh yes they can, because they are returned by another query that sorts it out. i could have added a column named 'transaction' with values (1, 78, 54, 13, 14, 15) for example that could be used as a primary key. sorry i didn't specify this:) |
|
|
|
petit-pere
Starting Member
12 Posts |
Posted - 2008-03-17 : 05:14:02
|
| sometimes you need to be proactiv :)i solved the problem in doing a manual calculation of all the rows in a worksheet. 3 hours of work with tests and controls.so i'm really sorry i can't post an elegant sql way for solving this problem.anyway thanks for your help elancaster! |
|
|
|
|
joining rows