How to get the value from another row in my query

Question

How to get value from another row in SQL?

ID  DeletedPersonID  ProductKey    PartyID       RespID  
9     461               17            33           95      
10    95                17            34           95

and I have this select statement

select
      drp.ID,
      drp.DeletedPersonID,
      drp.ProductID,
      case when drp.DeletedPersonID != drp.RespID then (Get Party Key by RespID) else drp.PartyID end as 'PartyID',
      case when drp.DeletedPersonID != drp.RespID then (Get Party Key by RespID) else drp.PartyName end as 'PartyName',
      case when drp.DeletedPersonID = drp.RespID then null else drp.PartyID end as 'SubPartyID',
from dbo.RespHistory drp

In above example since ID = 10 has the same DeletedPersonID and RespID value I like to use the same PartyID but in ID = 9 or in the first line Since DeletedPersonID and RespID are difference I want to use PartyID of ID = 10

I am looking a view similar to shown below

 ID DeletedPersonID  ProductKey    PartyID       PartyName   SubPartyID
   9      461               17            34           ABC      33
  10      95                17            34           XYZ      null

Where is [PartyName] coming from? In order to help we need as many details, you can provide. Also, will the row you need the value from **always** be the immediately preceding row? — critical_error, Aug 12 '20 at 21:21

score 1 · Accepted Answer · edited Aug 12 '20 at 23:21

1

Something like this

with equals_cte (RespID) as (
    select RespID
    from dbo.RespHistory
    where DeletedPersonID = RespID
)
select
    drp.ID, drp.DeletedPersonID, drp.ProductKey, coalesce(ec.RespID, drp.RespID) PartyID
    , drp.PartyName, drp.RespID
    , iif(drp.DeletedPersonID = drp.RespID, null, drp.RespID) SubPartyID
from dbo.RespHistory drp
left join equals_cte ec on drp.RespID = ec.RespID;

edited Aug 12 '20 at 23:21

Dale K

25,246
15
42
71

answered Aug 12 '20 at 21:30

SteveC

5,955
2
11
24

Thank you Steve, Can i create a view using `;with equals_cte`? showing me expecting incorrect syntax near ; `( or Select)` – Rob Aug 12 '20 at 21:52
Yes you can create a view. See [here](https://stackoverflow.com/questions/27172801/ms-sql-server-how-to-create-a-view-from-a-cte) – SteveC Aug 12 '20 at 22:04
1

FYI, `with` doesn't start with a `;` - `with` just requires the previous statement (if it exists) to be terminated with a `;` which is the recommended way to terminate *ALL* statements in T-SQL. – Dale K Aug 12 '20 at 23:22

How to get the value from another row in my query

1 Answers1