如何在SQL中将表的行显示为列

Question

我有两张桌子LANGUAGE

CREATE TABLE LANGUAGE
(
    LANGUAGE_ID nvarchar(10)
);

INSERT INTO LANGUAGE (LANGUAGE_ID)
VALUES ('de-DE'), ('en-EN'), ('de-US'), ('fr-FR'), ('es-ES');

和一个表TRANSLATION，它包含多种语言的TEXT_ID的所有翻译：

CREATE TABLE TRANSLATION
(
    TEXT_ID nvarchar(20), 
    LANGUAGE_ID varchar(10), 
    TRANSLATION varchar(200)
);

INSERT INTO TRANSLATION (TEXT_ID, LANGUAGE_ID, TRANSLATION)
VALUES
    ('wire', 'de-DE', 'Draht'),
    ('wire', 'en-EN', 'Wire'),
    ('wire', 'en-US', 'Wire'),
    ('wire', 'fr-FR', 'fr:Draht'),
    ('wire', 'es-ES', 'es:Draht'),
    ('brush', 'de-DE', 'Buerste'),
    ('brush', 'en-EN', 'Brush'),
    ('brush', 'en-US', 'us_Brush'),
    ('screw', 'en-US', 'Screw');

因此，我希望在一行中获得一个TEXT_ID的所有翻译的列表：

TEXT_ID     de-DE       en-EN       en-US       fr-FR       es-ES
--------------------------------------------------------------------
wire        Draht       Wire        Wire        fr:Draht    es:Draht
brush       Buerste     Brush       us_Brush
screw                               Screw

是否有一种纯粹的SQL方式来做到这一点？

目前我正在使用Server。但我们有可能想要迁移到另一个数据库。

示例SQLFiddle

Answer 1

我们可以使用一些日历表逻辑来尝试枢轴查询。首先，构建一个包含所有关键字和语言的中间表。然后，将其连接到TRANSLATION表和支点。

SELECT
    t.TEXT_ID,
    MAX(CASE WHEN l.LANGUAGE_ID = 'de-DE' THEN tr.TRANSLATION END) AS [de-DE],
    MAX(CASE WHEN l.LANGUAGE_ID = 'en-EN' THEN tr.TRANSLATION END) AS [en-EN],
    MAX(CASE WHEN l.LANGUAGE_ID = 'en-US' THEN tr.TRANSLATION END) AS [en-US],
    MAX(CASE WHEN l.LANGUAGE_ID = 'fr-FR' THEN tr.TRANSLATION END) AS [fr-FR],
    MAX(CASE WHEN l.LANGUAGE_ID = 'es-ES' THEN tr.TRANSLATION END) AS [es-ES]
FROM (SELECT DISTINCT TEXT_ID FROM TRANSLATION) t
CROSS JOIN LANGUAGE l
LEFT JOIN TRANSLATION tr
    ON tr.TEXT_ID = t.TEXT_ID AND
       tr.LANGUAGE_ID = l.LANGUAGE_ID
GROUP BY t.TEXT_ID;

请注意，如果需要动态列数，则可能需要使用动态SQL，这基本上意味着编写SQL Server代码来生成查询。