samedi 9 mai 2015

SQL Server 2008 R2: Tricky query - Finding certain rows in segments of rows

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