need to get a subsequent record with a specific value

i have the following table :

        Dt          Status
05.23.2019 10:00:00   A
05.23.2019 11:00:00   B
05.23.2019 12:00:00   B
05.23.2019 13:00:00   D
05.23.2019 14:00:00   A
05.23.2019 15:00:00   B
05.23.2019 16:00:00   C
05.23.2019 17:00:00   D
05.23.2019 18:00:00   A

For each status A i need to get the next status D. The result should be like this :

Status1   Status2  Dt1                  Dt2
A         D        05.23.2019 10:00:00  05.23.2019 13:00:00
A         D        05.23.2019 14:00:00  05.23.2019 17:00:00
A         null     05.23.2019 18:00:00  null

I have my own solution based on cross/outer apply , In terms of performance i need solution without cross/outer apply.

Answer

We can try using ROW_NUMBER here along with some pivot logic:

WITH cte AS (
    SELECT *,
        ROW_NUMBER() OVER (PARTITION BY Status ORDER BY Dt) rn
    FROM yourTable
    WHERE Status IN ('A', 'D')
)

SELECT
    MAX(CASE WHEN Status = 'A' THEN Status END) AS Status1,
    MAX(CASE WHEN Status = 'D' THEN Status END) AS Status2,
    MAX(CASE WHEN Status = 'A' THEN Dt END) AS Dt1,
    MAX(CASE WHEN Status = 'D' THEN Dt END) AS Dt2
FROM cte
GROUP BY rn
ORDER BY rn;

Demo

The idea here is to generate a row number sequence along your entire table, for each separate Status value (A or D). Then, aggregate by that row number sequence to bring the A and D records together.