Thanks Sean.. here is the query I have written.

DECLARE @Tab1 table (ID Int , PS varchar(10),NPS1 varchar(10), NPS2 varchar(10))

INSERT INTO @Tab1

SELECT 1, 'A', 'B', 'C'

UNION ALL

SELECT 2, 'D', 'E', 'F'

UNION ALL

SELECT 3, 'G', 'H', 'I'

UNION ALL

SELECT 4, 'J', 'K', 'L'

Select * FROm @Tab1

DECLARE @TAB2 TABLE (ID INT , SV varchar(10), Col1 varchar(10), Col2 Varchar(10))

INSERT INTO @Tab2

SELECT 1, 'C', 'XYZ', '123'

UNION ALL

SELECT 2, 'D', 'AAA', '111'

UNION ALL

SELECT 3, 'J', 'BBB', '222'

Select * From @Tab2

Select A.PS,

CASE WHEN A.PS = B.SV THEN B.Col1 END AS PS_COl1,

CASE WHEN A.PS = B.SV THEN B.Col2 END AS PS_COl2, A.NPS1,

CASE WHEN A.NPS1 = B.SV THEN B.Col1 END AS NPS1_COl1,

CASE WHEN A.NPS1 = B.SV THEN B.Col2 END AS NPS1_COl2,A.NPS2,

CASE WHEN A.NPS2 = B.SV THEN B.Col1 END AS NPS2_COl1,

CASE WHEN A.NPS2 = B.SV THEN B.Col2 END AS NPS2_COl2

FROM @Tab1 A

INNER JOIN @Tab2 B ON A.PS = B.SV OR A.NPS1 = B.SV OR A.NPS2 = B.SV