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
精彩评论