By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE T(
LastYardBay VARCHAR(10),
LastYardRow VARCHAR(10),
MaxTier INT
);
INSERT INTO T VALUES
('005', '00A', 3),
('005', '00B', 4),
('005', '00C', 1),
('005', '00D', 1),
('005', '00E', 1),
('005', '00F', 4),
('005', '00G', 1),
('007', '00E', 5),
('007', '00F', 4),
('007', '00G', 1);
10 rows affected
SELECT *
FROM T
WHERE LastYardBay = '005'
UNION
SELECT '007',
T1.LastYardRow,
ISNULL(T2.MaxTier, 0)
FROM
(
SELECT *
FROM T
WHERE LastYardBay = '005'
) T1
LEFT JOIN
(
SELECT *
FROM T
WHERE LastYardBay = '007'
) T2
ON T1.LastYardRow = T2.LastYardRow;
| LastYardBay | LastYardRow | MaxTier |
|---|---|---|
| 005 | 00A | 3 |
| 005 | 00B | 4 |
| 005 | 00C | 1 |
| 005 | 00D | 1 |
| 005 | 00E | 1 |
| 005 | 00F | 4 |
| 005 | 00G | 1 |
| 007 | 00A | 0 |
| 007 | 00B | 0 |
| 007 | 00C | 0 |
| 007 | 00D | 0 |
| 007 | 00E | 5 |
| 007 | 00F | 4 |
| 007 | 00G | 1 |