Sql Eliminate Row When Null

I have data from Table1: AgeCount Age GenderCount Gender RaceCount Race ----------------------------------------------------------------------------- 12

Solution 1:

Based on the information provided, the following query produces the desired results:

• It joins 3 sub-queries, 1 for each of the non-null components (Age, Race, Gender)
• It uses a row_number() to give an arbitrary join condition
• It uses a full outer join to handle the case when there might be more on one type of value than another e.g. if you added another row of of age count

declare@Testtable (AgeCount int, Age varchar(16), GenderCount int, Gender varchar(1), RaceCount int, Race varchar(64));

    insertinto@Test (AgeCount, Age, GenderCount, Gender, RaceCount, Race)
    values (12, '1-10', null, null, null, null)
    , (10, '11-20', null, null, null, null)
    , (null, null, 3, 'M', null, null)  
    , (null, null, 5, 'F', null, null)
    , (null, null, null, null, 20, 'American Indian')
    , (null, null, null, null, 10, 'Africa');

    select AgeCount, Age, GenderCount, Gender, RaceCount, Race
    from (
      select AgeCount, Age
        , row_number() over (orderby AgeCount) row#
      from@Testwhere AgeCount isnotnull
    ) X
    fullouterjoin
    (
      select GenderCount, Gender
        , row_number() over (orderby GenderCount) row#
      from@Testwhere GenderCount isnotnull
    ) Y on Y.row# = X.row#
    fullouterjoin (
      select RaceCount, Race
        , row_number() over (orderby RaceCount) row#
      from@Testwhere RaceCount isnotnull
    ) Z on Z.row# = X.row#;

Gives the following:

AgeCount    Age     GenderCount Gender  RaceCount   Race
-------------------------------------------------------------------
10          11-20   3           M       10          Africa
12          1-10    5           F       20          American Indian