0

Consider the following table,

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)  
TabName     PersonID     FirstName    BenefitType      HealthStatus 
TAB1        C103         John         Benefit Type 1   NULL   
TAB2        C103         John         NULL             Healthy
TAB3        C103         John         NULL             Healthy
TAB1        C104         Mary         Benefit Type 2   NULL   
TAB2        C104         Mary         NULL             Sick   
TAB3        C104         Mary         NULL             NULL   

I would like to merge the information across TabName in a new table, such that the new table contains all the unique information for each PersonID.

For example, based on the above table, I would like to find the new table

PersonID   FirstName    BenefitType      HealthStatus   
C103       John         Benefit Type 1   Healthy  
C104       Mary         Benefit Type 2   Sick   

I am not too sure where to start with this problem.

4

Can you have , in column BenefitType , multiples unique information, for the same Person ?
if no(only one unique value/status), you can use MAX ,
else (u can have more unique values) - you must provide more information , an algorithm to how to retrieve the desired value .

The same , for for the column HealthStatus.

SELECT
    [PersonID] ,
    [FirstName],
    [Gender],
    MAX([BenefitType]) as BenefitType,
    MAX([HealthStatus]) as HealthStatus
 FROM Temp AS T
 GROUP BY
    [PersonID] ,
    [FirstName],
    [Gender]

Output:

PersonID    FirstName   Gender  BenefitType     HealthStatus
C103        John        M       Benefit Type 1  Healthy
C104        Mary        M       Benefit Type 2  Sick

dbfiddle here

2
  • For each PersonID there will be only one BenefitType value.
    – TMilliman
    Dec 15 '19 at 5:37
  • Great , then MAX will do the job
    – Sabin B
    Dec 15 '19 at 5:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.