Need Help to create query for below requirement-CodePudding

MY Table T1

CID  Code   Addr    City              State
S10   10     20   bangalore        Karnataka
S20   20     35   Hyderabad        Telangana

My result set should be like

col 3 Addr refernce 20 is a value of colum2 Code in 2nd row and the result set should be in one row for both the rows

CID     Code  Addr    City          State          City       State
S10     10    20      Bangalore     Karnataka      Hyderabad  Telangana

CodePudding user response：

declare @table table (
  CID varchar(20),
  Code varchar(20),
  Addr varchar(20),
  City varchar(20),
  State varchar(20)
)

insert into @table SELECT 'S10','10','20','bangalore','Karnataka'
insert into @table SELECT 'S20','20','35','hyderabad','Telangana'

    
SELECT
  t1.CID,
  T1.Code,
  T1.Addr,
  T1.City,
  T1.State,
  t2.city,
  t2.State
FROM (SELECT Code from @table where addr in (select code from @table) group by code ) codesTable
inner join @table t1 on codesTable.Code = t1.Code
 inner join @table t2 on t1.addr = t2.Code

Self joins but specifying the addr column must exist as a code column

CodePudding user response：

This is a straight-forward self-join, for your desired results you just need:

select t1.*, t2.city, t2.state
from t t1
join t t2 on t2.code=t1.addr