Results 1 to 5 of 5
  1. #1
    Join Date
    Feb 2006
    Posts
    8

    Question Unanswered: Combine 2 rows into 1 row - replacing NULL values in one with NON-NULL in other

    Consider the two following SQL Statements

    Code:
    select null as value1, 1 as value2
    Code:
    select 1 as value1, null as value2
    I know there's a very simple way to combine these resultsets into a single row. I just can't remember how to do it.

    The result would be.

    Code:
    value 1 | value 2
    -----------------
    1       | 2
    where the NULL values get replaced by the NON-NULL values.

    Thanks a lot in advance!

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Assuming there is never a case when both are not null then a derived table UNION with MAX() aggregate function will do it.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    intGod, where did the two come from???

    Poots, I'm not so sure about that...

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Will this do?
    Code:
    select
    Value1 =coalesce(t1.value1,t2.value1)
    ,Value2 =coalesce(t1.value2,t2.value2)
    from (
       select null as value1, 1 as value2) t1
    cross join (
       select 1 as value1,null as value2) t2
    Except the result will be different from the one that you posted, for obvious reasons.
    Last edited by rdjabarov; 07-06-10 at 15:22. Reason: formatting
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Pat Phelan View Post
    intGod, where did the two come from???

    Poots, I'm not so sure about that...
    Ignoring the two bit....
    Code:
    SELECT  value1      = MAX(value1)
          , value2      = MAX(value2)
    FROM    
            (
                SELECT  NULL    AS value1
                      , 1       AS value2
                UNION ALL
                SELECT  1       AS value1
                      , NULL    AS value2
            ) AS x
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •