CODEWITHSUNDEEP
sqlsql-server-2012self-join
03Usr
03Usr

Reputation: 3435

SQL - Get corresponding column based on another row in the same table

I have the following table:

id    u_Id   Ref    c_type
1      1    ref1      c
2      1    ref2      c
3      1    ref3      m

What I need to do is for the same uid (in this case 1) where the group_id is c get the corresponding row where the group_id is m. So for both id 1 and 2 columns I need to get the ref from row with id 3.

I have tried this and it seems to be working but I was wondering if there another shorter version possible:

Declare @temp table (id int, u_id int, ref varchar(10), c_type varchar(1))

insert into @temp 
select 1, 1, 'ref1', 'c' union all
select 2, 1, 'ref2', 'c' union all
select 3, 1, 'ref3', 'm'

;with b as
(
    select * from @temp
    where c_type = 'c'
),
x as
(
    select * from @temp
    where c_type = 'm'
)
select distinct x.u_id,x.c_type,x.ref from b,x
where x.u_id = b.u_id

Expected results is

id    u_Id   Ref    c_type    ref
1      1    ref1      c      ref3
2      1    ref2      c      ref3

Upvotes: 0

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270653

You can do this with a window function:

select t.*,
       max(case when c_type = 'm' then id end) over (partition by u_id) as m_id;
from @temp t;

If you just want the ref column:

select t.*,
       max(case when c_type = 'm' then ref end) over (partition by u_id) as m_ref;
from @temp t;

If you need other values from the row, you can join them in:

select t.*, tm.*
from (select t.*,
             max(case when c_type = 'm' then id end) over (partition by u_id) as m_id;
      from @temp t
     ) t left join
     @temp tm
     on t.m_id = tm.id;

Upvotes: 1

Related Questions