SQL 联合查询按字段分组并投影到扁平结果集中

Question

我正在尝试编写一个查询，该查询将从具有不同条件的同一个表中检索记录，并以如下所示的扁平格式显示结果:

期望的结果

ID, Word, Translation_From_Region_1007, Translation_From_Region_1006

1, Word1, Test 1, Test 2

2, Word2, Test 3, Test 4

下面是我查询的伪代码，但是我不完全确定如何展平结果以显示我想要的结果:

SELECT     Words.ID, Words.Word, Translation
FROM         Words  WHERE RegionId=1007
UNION 
SELECT     Words.ID, Words.Word, Translation
FROM         Words WHERE RegionId=1006

Group by Word (as I only want one instance of the word itself with its respective translations flattened.

如果有人能给我任何建议或提出更好的方法，我将不胜感激。

Answer 1

这个怎么样？

select word, max(Translation1006), max(Translation1007)
from
   (SELECT     
       words.word,
       Translation1006 = 
       CASE region
        when 1006 THEN trans
        else NULL
    END,  
    Translation1007 =
    CASE region
        when 1007 THEN trans
        else NULL
    END
    FROM
       words) as detail
group by word