By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE temp (
[TabName] VARCHAR(255),
[PersonID] VARCHAR(255),
[FirstName] VARCHAR(255),
[Gender] VARCHAR(255),
[BenefitType] VARCHAR(255),
[HealthStatus] VARCHAR(255));
INSERT INTO temp VALUES
('TAB1', 'C103','John', 'M', 'Benefit Type 1', NULL),
('TAB2', 'C103','John', 'M', NULL, 'Healthy'),
('TAB3', 'C103', 'John', 'M', NULL, 'Healthy'),
('TAB1', 'C104', 'Mary', 'M', 'Benefit Type 2', NULL),
('TAB2', 'C104', 'Mary', 'M', NULL, 'Sick'),
('TAB3', 'C104', 'Mary', 'M', NULL, NULL)
SELECT
[PersonID] ,
[FirstName],
[Gender],
MAX([BenefitType]) as BenefitType,
MAX([HealthStatus]) as HealthStatus
FROM Temp AS T
GROUP BY
[PersonID] ,
[FirstName],
[Gender]
| PersonID | FirstName | Gender | BenefitType | HealthStatus |
|---|---|---|---|---|
| C103 | John | M | Benefit Type 1 | Healthy |
| C104 | Mary | M | Benefit Type 2 | Sick |
Warning: Null value is eliminated by an aggregate or other SET operation.