SQL UNION and MERGE
12,760
Solution 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
Solution 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
Author by
shockwave
Updated on June 28, 2022Comments
-
shockwave almost 2 years
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
orMERGE
? 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 | +----+------+---------------+