SQL 将关系表合并为单独的列

Question

我有这两张 table

 Items
 ID     Type     ClientID
 ========================
 1      0        123
 2      0        123

 Texts
 ItemID   Language     Text
 ================================
 1        Eng          Hi there!
 1        Spa          Holla!
 2        Eng          bye!
 2        Spa          bye in Spanish!

在我的最终结果中，我等待 SQL 返回该表

 ID      Type    Eng           Spa
 ================================
 1       0        Hi there!     Holla!
 2       0         Bye!          bye in Spanish!

我尝试创建此语句:

 SELECT DISTINCT I.ID ,I.Type,
 (SELECT T.Text WHERE D.Language='Eng') AS 'Eng',
 (SELECT T.Text WHERE D.Language='Spa') AS 'Spa'
 FROM Items I
 INNER JOIN Texts T ON I.ID=T.ItemID

但我得到这个结果:

 ID      Type    Eng           Spa
 ================================
 1       0        Hi there!     NULL
 1       0        NULL          Holla!
 2       0        Bye!          NULL
 2       0        NULL          bye in Spanish!

Answer 1

我不明白为什么需要加入。您可以只使用条件聚合:

select t.itemid,
       max(case when t.language = 'Eng' then t.text end) as Eng,
       max(case when t.language = 'Spa' then t.text end) as Spa
from texts t
group by t.itemid;