T-SQL Combine Multiple Rows Into Single Row

I have this T-SQL (simplified):

select 3.00 as score1, null as score2, null as score3, null as score4
union all
select null as score1, 4.5 as score2, 1.5 as score3, null as score4

Which produces this:

score1  score2  score3  score4
------------------------------
3.00    NULL    NULL    NULL
NULL    4.5     1.5     NULL

But i want to join it into one row, like this:

score1  score2  score3  score4
------------------------------
3.00    4.5     1.5     NULL

Sorry - im drawing blank (it开发者_运维问答's late in the day).

Do i need a temporary table?

The reason i'm doing this is that i have the following association:

Reviews 1..* Scores

So a regular join produces 1 row for each Score - but i want to insert a record (into another table), that has a column for each record - if you know what i mean:

INSERT INTO OtherTable (ReviewId, Score1, Score2, Score3, Score4)
????

Hope that makes sense.

EDIT

Based on @OMG Ponies answer (which he just removed), i came up with this:

SELECT CASE MAX(x.score1) WHEN 0 THEN NULL ELSE MAX(x.score1) END AS score4
       CASE MAX(x.score2) WHEN 0 THEN NULL ELSE MAX(x.score2) END AS score4
       CASE MAX(x.score3) WHEN 0 THEN NULL ELSE MAX(x.score3) END AS score4
       CASE MAX(x.score4) WHEN 0 THEN NULL ELSE MAX(x.score4) END AS score4
  FROM (select 3.00 as score1, 0 as score2, 0 as score3, 0 as score4
        union all
        select 0 as score1, 4.5 as score2, 1.5 as score3, 0 as score4) x

But it's pretty ugly. Any other ideas?

---

Under assumption that only one of both rows has a value in score1, score2, ... Otherwise rewrite your query using the NULLIF function.

SELECT
    NULLIF(ISNULL(NULLIF(x.score1, 0), y.score1), 0) score1,
    NULLIF(ISNULL(NULLIF(x.score2, 0), y.score2), 0) score2,
    NULLIF(ISNULL(NULLIF(x.score3, 0), y.score3), 0) score3,
    NULLIF(ISNULL(NULLIF(x.score4, 0), y.score4), 0) score4
FROM (SELECT 3.00 AS score1, 0 AS score2, 0 AS score3, 0 AS score4) x
CROSS JOIN (SELECT 0 AS score1, 4.5 AS score2, 1.5 AS score3, 0 AS score4) y

---

The @OMG Ponies/RPM1984 query seems to work:

SELECT MAX(x.score1),
       MAX(x.score2),
       MAX(x.score3),
       MAX(x.score4)
  FROM (select 3.00 as score1, null as score2, null as score3, CAST(null as int) as score4
        union all
        select null as score1, 4.5 as score2, 1.5 as score3, null as score4) x

Which results in:

3.00    4.5 1.5 NULL

I'm not sure what you'd do if multiple rows define the same score.

---

I have to add CAST(null as int) to the fourth column, because otherwise there's no way for SQL Server to work out the type of the fourth column - all it has are two nulls, and nulls can be of any type.

---

When I try it (SQL 2008) using a simple MAX() it seems to work:

SELECT 
    MAX(score1),
    MAX(score2),
    MAX(score3),
    MAX(score4)
FROM
    (select 3.00 as score1, null as score2, null as score3, null as score4 
    union all 
    select null as score1, 4.5 as score2, 1.5 as score3, null as score4) s

I ran this test

declare @t table (i int null)
insert @t values (null)
insert @t values (1)
select MAX(i) from @t

Is this what you want to do?

---

Ended up going with my original query (thanks to @OMG Ponies for putting me on the right track):

SELECT CASE MAX(x.score1) WHEN 0 THEN NULL ELSE MAX(x.score1) END AS score4
       CASE MAX(x.score2) WHEN 0 THEN NULL ELSE MAX(x.score2) END AS score4
       CASE MAX(x.score3) WHEN 0 THEN NULL ELSE MAX(x.score3) END AS score4
       CASE MAX(x.score4) WHEN 0 THEN NULL ELSE MAX(x.score4) END AS score4
  FROM (select 3.00 as score1, 0 as score2, 0 as score3, 0 as score4
        union all
        select 0 as score1, 4.5 as score2, 1.5 as score3, 0 as score4) x