CODEWITHSUNDEEP
sql-servert-sqlsql-server-2008view
Rob
Rob

Reputation: 225

How to get the value from another row in my query

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

Upvotes: 1

Views: 56

Answers (1)

SteveC
SteveC

Reputation: 6015

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;

Upvotes: 1

Related Questions