Exclude blank column while Joining two SQL server tables

I have set of vehicle parts stored in two tables as per below:

Source Table1:

Vehicle_ID	Part1	Part2	Part3	Part4	Part5
1	10	20	30
2	10	20
3	10

Source Table2:

Vehicle_ID	Part6	Part7	Part8	Part9	Part10
1	40
2	30	50	60
3	30

Required Table like below:

Vehicle_ID	Part1	Part2	Part3	Part4	Part5
1	10	20	30	40
2	10	20	30	50	60
3	10	30

Maximum of the Part column up to 5 only.

I tried Union all statement but that statement is not relevant for excluding blank columns.

Please share your experience to solve my problem .

Answer

You need to unpivot and pivot the rows in both tables.

Data:

SELECT *
INTO Table1
FROM (VALUES
   (1, 10, 20,   30,   NULL, NULL), 
   (2, 10, 20,   NULL, NULL, NULL), 
   (3, 10, NULL, NULL, NULL, NULL),     
   (4, 40, NULL, NULL, NULL, NULL)      
) v (Vehicle_ID, Part1, Part2, Part3, Part4, Part5)

SELECT *
INTO Table2
FROM (VALUES
   (1, 40, NULL, NULL, NULL, NULL),     
   (2, 30, 50,   60,   NULL, NULL),
   (3, 30, NULL, NULL, NULL, NULL),
   (5, 50, NULL, NULL, NULL, NULL)
) v (Vehicle_ID, Part6, Part7, Part8, Part9, Part10)

Statement:

SELECT 
   Vehicle_ID,
   MAX(CASE WHEN RN = 1 THEN Part END) AS Part1,
   MAX(CASE WHEN RN = 2 THEN Part END) AS Part2,
   MAX(CASE WHEN RN = 3 THEN Part END) AS Part3,
   MAX(CASE WHEN RN = 4 THEN Part END) AS Part4,
   MAX(CASE WHEN RN = 5 THEN Part END) AS Part5
FROM (
   SELECT 
      COALESCE(t1.Vehicle_ID, t2.Vehicle_ID) AS Vehicle_ID,
      a.*,
      ROW_NUMBER() OVER (PARTITION BY COALESCE(t1.Vehicle_ID, t2.Vehicle_ID) ORDER BY a.Part_ID) AS RN
   FROM Table1 t1
   FULL OUTER JOIN Table2 t2 ON t1.Vehicle_ID = t2.Vehicle_ID
   CROSS APPLY (VALUES
      (1, t1.Part1),
      (2, t1.Part2),
      (3, t1.Part3),
      (4, t1.Part4),
      (5, t1.Part5),
      (6, t2.Part6),
      (7, t2.Part7),
      (8, t2.Part8),
      (9, t2.Part9),
      (10, t2.Part10)
   ) a (Part_ID, Part)
   WHERE a.Part IS NOT NULL
) t
GROUP BY Vehicle_ID

Result:

Vehicle_ID	Part1	Part2	Part3	Part4	Part5
1	10	20	30	40
2	10	20	30	50	60
3	10	30
4	40
5	50