T-SQL将多行合并为单行 [英] T-SQL Combine Multiple Rows Into Single Row

问题描述

我有这个T-SQL（简体）：

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

会产生以下内容：

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).

我需要一个临时表吗？

我这样做的原因是我有以下内容关联：

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

评论1 .. *得分

因此常规联接产生每个分数都有1行-但是我想插入一条记录（到另一个表中），每条记录都有一个列-如果您知道我的意思，则：

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)
????

希望如此。

编辑

基于@OMG Ponies答案（他刚刚删除了它），我想到了：

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?

推荐答案

最终完成了我的原始查询（感谢@OMG Ponies，使我处于正确的轨道）：

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

这篇关于T-SQL将多行合并为单行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！