SQL UNION and MERGE

Question

I'm having multiple select statements involving many tables and Joins. All the select statements have the same Headers. I'm trying to combine all this into a single result set. So which approach is better SQL UNION or MERGE? I know UNION is A+B. So if a column is NULL in Table A and it has a value in TABLE B then UNION will give me two rows right? So if I want to combine all the rows into a single row based on the id should I use MERGE? I have an option to do this in SQL or SSIS.

SELECT ID, NAME, VitalName as VitalName FROM TABLE A
UNION
SELECT ID, NAME, VitalReadings as VitalName FROM TABLE B

TableA

+----+------+-----------+
| ID | Name | VitalName |
+----+------+-----------+
|  1 | AAA  | HeartRate |
|  2 |      | Systolic  |
|  3 |      | Diastolic |
+----+------+-----------+

TableB

+----+------+---------------+
| ID | Name | VitalReadings |
+----+------+---------------+
|  1 | AAA  | HeartRate     |
|  2 | BBB  | Systolic      |
+----+------+---------------+

Expected Result

+----+------+---------------+
| ID | Name | VitalName     |
+----+------+---------------+
|  1 | AAA  | HeartRate     |
|  2 | BBB  | Systolic      |
|  3 |      | Diastolic     |
+----+------+---------------+

Answer 1

UNION and MERGE totally different concepts and both not solves your problem. But can use FULL JOIN for making this.

DECLARE @TableA TABLE (ID INT,  Name VARCHAR(10), VitalName VARCHAR(10))
INSERT INTO @TableA VALUES
(1,'AAA','HeartRate'),
(2,NULL,'Systolic'),
(3,NULL,'Diastolic')

DECLARE @TableB TABLE ( ID INT,  Name VARCHAR(10), VitalReadings VARCHAR(10))

INSERT INTO @TableB VALUES
(1,'AAA','HeartRate'),
(2,'BBB','Systolic')


SELECT 
    A.ID, 
    COALESCE(A.Name, B.Name) Name,
    COALESCE(A.VitalName, B.VitalReadings) VitalName
FROM 
    @TableA A 
    FULL JOIN @TableB B ON A.ID = B.ID

Result:

ID          Name       VitalName
----------- ---------- ----------
1           AAA        HeartRate
2           BBB        Systolic
3           NULL       Diastolic

Answer 2

GROUP BY the UNION's result. Use MAX() to return the NAME:

select ID, MAX(NAME), VitalName 
from
(
    SELECT ID, NAME, VitalName as VitalName FROM TABLE A
    UNION ALL
    SELECT ID, NAME, VitalReadings as VitalName FROM TABLE B
) dt
group by ID, VitalName