CODEWITHSUNDEEP
sql-servert-sql
jigga
jigga

Reputation: 614

Select data from previous rows with condition

My data looks like this

    col1; col2; col3; col4; sort; desc; levels
    1     1     1     1     10    ab     null
    1     1     1     2     20    c      1
    1     1     1     3     30    d      null
    1     1     1     4     40    e      null
    1     1     1     5     50    fg     1
    1     1     1     6     60    hij    1
    1     1     1     7     70    k      1

The desired output looks like this

    col1; col2; col3; col4; desc; addDesc
    1     1     1      1     ab   null
    1     1     1      2     c    ab
    1     1     1      3     d    null
    1     1     1      4     e    null
    1     1     1      5     fg   e
    1     1     1      6     hij  e
    1     1     1      7     k    e

The logic is whenever the column LEVELS is not null, take the value from previous DESC where LEVELS is null. I hope you guys understand this!

Thank you all in advance!

Upvotes: 0

Views: 48

Answers (1)

Lamak
Lamak

Reputation: 70638

You can use OUTER APPLY:

SELECT  t1.col1,
        t1.col2,
        t1.col3,
        t1.col4,
        t1.[desc],
        CASE
            WHEN t1.levels IS NOT NULL THEN t2.[desc]
        END [addDesc]
FROM dbo.YourTable t1
OUTER APPLY (SELECT TOP 1 [desc]
             FROM dbo.YourTable
             WHERE levels IS NULL
             AND sort < t1.sort
             ORDER BY sort DESC) t2;

Upvotes: 2

Related Questions