How to join a column with multiple columns

[Dilver_Shaik]

Hi Team,

I have a below requirement. 

Table 1:

id1	id1_comm	id2	id2_comm	id3	id3_comm	id4	id4_comm
1001	Comment1
		1002	Comment2
1003	Comment1			1003	Comment3
1004	Comment1	1004	Comment2	10004	Comment3	1004	Comment4

Table 2:

id	idName
1001	Test1
1002	Test2
1003	Test3
1004	Test4
1005	Test5

Expected Output

id1	id1_comm	id2	id2_comm	id3	id3_comm	id4	id4_comm	id	idName
1001	Comment1							1001	Test1
		1002	Comment2					1002	Test2
1003	Comment1			1003	Comment3			1003	Test3
1004	Comment1	1004	Comment2	10004	Comment3	1004	Comment4	1004	Test4

How can I join the data comparing with multiple columns as the data may or may not present in all those columns. Sometimes the data is present in all the 4 columns.

Sorry that I couldn't provide the data in an excel as I cant upload the file due to restrictions. Any help here would be appreciated. Thanks in advance.

Thanks & Regards

Dilver

[Raj · Accepted solution]

PFA

[ChrisTX]

One option is attached.

 

Chris

[Dilver_Shaik]

Thanks Raj that works