Remove Duplicate columns from table in Full Outer Join

Question

I have two tables A and B. Both tables have 58 columns per table.

Table "A" has Column1, Column2, Column3, Column4, Column5, A1, A2, A3,...,A53.

Table "B" has Column1, Column2, Column3, Column4, Column5, B1, B2, B3,...,B53.

I have used FULL OUTER JOIN. And I have output,

My Output table "MOT" has Column1, Column2, Column3, Column4, Column5, A1, A2, A3,...,A53, Column1, Column2, Column3, Column4, Column5, B1, B2, B3,...,B53.

I want following output.

Output Table "OT" has Column1, Column2, Column3, Column4, Column5, A1, A2, A3,...,A53, B1, B2, B3,...,B53.

So i do not want duplicate columns in the output.

I appreciate your help. Thank you.

Answer 1

You would need to decide if you wanted the columns [Column1, Column2, Column3, Column4, Column5] From table A or B and alias appropriately.

Example: Alias Table 'A' as 'a' then

Select

a.Column1,a.Column2,a.Column3, a.Column4, a.Column5, A1, A2, A3,...,A53, B1, B2, B3,...,B53

Answer 2

I used loop and generate dynamic query. This will exclude the B.Column1, B.Column2, B.Column3, B.Column4, B.Column5 from the table.

DECLARE @sel VARCHAR(MAX)
DECLARE @columnName VARCHAR(MAX)
DECLARE @temp VARCHAR(MAX)
DECLARE @frm VARCHAR(MAX)
DECLARE @counter INT

SET @counter = 1
SET @sel = 'select A.*';
SET @temp = '';
SET @frm = ' from table';
SET @columnName = ' , B';

WHILE @counter <= 53
    BEGIN
        SET @temp = @temp + @columnName + cast(@counter as varchar(4));
        SET @counter = @counter + 1;
    END
SET @columnName = @sel + @temp + @frm;
EXEC(@columnName);