如何从PRODUCT_DESCRIPTIONS表查询美俄翻译字段并转为横向四列

UNION/UNION ALL的作用是纵向合并多个查询的结果集，天生就是上下堆叠的结构，要实现横向多列展示可以用下面两种常用方案：

方案1：条件聚合（推荐，性能更优，写法简单） #

只需要单次扫描表，通过CASE WHEN按语言ID拆分字段即可，如果你筛选后每个语言只会返回1条结果，直接用MAX聚合就能取出对应值：

SELECT
    MAX(CASE WHEN LANGUAGE_ID = 'US' THEN TRANSLATED_NAME END) AS US_NAME,
    MAX(CASE WHEN LANGUAGE_ID = 'US' THEN TRANSLATED_DESCRIPTION END) AS US_DESC,
    MAX(CASE WHEN LANGUAGE_ID = 'RU' THEN TRANSLATED_NAME END) AS RU_NAME,
    MAX(CASE WHEN LANGUAGE_ID = 'RU' THEN TRANSLATED_DESCRIPTION END) AS RU_DESCRIPTION
FROM PRODUCT_DESCRIPTIONS PD
WHERE 
    (LANGUAGE_ID = 'US' AND TRANSLATED_NAME LIKE '%Monitor%')
    OR (LANGUAGE_ID = 'RU' AND TRANSLATED_NAME LIKE '%Монитор%');

如果你的PRODUCT_DESCRIPTIONS表有公共的PRODUCT_ID字段对应同一款产品的多语言版本，还可以在末尾加GROUP BY PRODUCT_ID，一次性查出所有符合条件的产品的多语言对照。

方案2：子查询关联 #

把两个语言的查询结果作为独立的子查询，做关联即可实现横向拼接：

SELECT
    us.US_NAME,
    us.US_DESC,
    ru.RU_NAME,
    ru.RU_DESCRIPTION
FROM 
(
    SELECT 
        TRANSLATED_NAME AS US_NAME,
        TRANSLATED_DESCRIPTION AS US_DESC,
        PRODUCT_ID -- 有产品ID的话加上关联更精准
    FROM PRODUCT_DESCRIPTIONS PD
    WHERE PD.TRANSLATED_NAME LIKE '%Monitor%' 
    AND PD.LANGUAGE_ID = 'US'
) us
-- 如果有PRODUCT_ID就用INNER JOIN关联，没有的话用CROSS JOIN也可以实现两个单行结果的拼接
JOIN 
(
    SELECT
        TRANSLATED_NAME AS RU_NAME, 
        TRANSLATED_DESCRIPTION AS RU_DESCRIPTION,
        PRODUCT_ID
    FROM PRODUCT_DESCRIPTIONS PD
    WHERE  PD.LANGUAGE_ID = 'RU'
    AND PD.TRANSLATED_NAME LIKE '%Монитор%'
) ru
ON us.PRODUCT_ID = ru.PRODUCT_ID; -- 没有PRODUCT_ID的话删掉这行ON条件，把JOIN改成CROSS JOIN就行

内容的提问来源于stack exchange，提问作者NNikolow