I don't know how to explain this in a general way, so allow me to give an example.
select ParentId, Id, Number
from SomeTable
order by ParentId, Id
gives
ParentId Id Number
2997 1145445 400
2997 1145449 400
2997 1145577 400
2997 1146518 405
2999 1145470 400
2999 1145502 400
2999 1145504 400
3016 1145633 400
3016 1145636 400
3016 1145677 400
3016 1145686 405
3037 1145885 400
3037 1145906 405
3037 1145922 400
3037 1145925 400
3162 1147324 400
3162 1147327 400
3162 1147329 400
3162 1147332 400
3162 1147334 405
3162 1147339 400
3162 1147341 405
3162 1147345 406
3162 1147347 410
(this is just a subset of the real data, but enough to explain the problem).
The problem:
A query (as elegant and efficient as possible, preferably without sub-queries, cursors etc.) that selects exactly all rows with Number = 400 but with the following columns added:
Id_405: The Id of the row with Number 405 and the same ParentId between this 400 and the next 400, ordered as above by Id. It is guaranteed that there is at most one 405. Id_405 is null if there is no such 405.
Id_406: The Id of the row with Number 406 and the same ParentId between this 400 and the next 400, ordered as above by Id. It is guaranteed that there is at most one 406. Id_406 is null if there is no such 406.
Id_410: The Id of the row with Number 410 and the same ParentId between this 400 and the next 400, ordered as above by Id. It is guaranteed that there is at most one 406. Id_410 is null if there is no such 410.
With the above example data, the query would return:
ParentId Id Number Id_405 Id_406 Id_410
2997 1145445 400 null null null
2997 1145449 400 null null null
2997 1145577 400 1146518 null null
2999 1145470 400 null null null
2999 1145502 400 null null null
2999 1145504 400 null null null
3016 1145633 400 null null null
3016 1145636 400 null null null
3016 1145677 400 1145686 null null
3037 1145885 400 1145906 null null
3037 1145922 400 null null null
3037 1145925 400 null null null
3162 1147324 400 null null null
3162 1147327 400 null null null
3162 1147329 400 null null null
3162 1147332 400 1147334 null null
3162 1147339 400 1147341 1147345 1147347
Please help!
Aucun commentaire:
Enregistrer un commentaire