SQL Server多语言翻译表动态列转行查询方案

问题背景 #

我有一张用于应用UI翻译的表，结构及测试数据如下：

测试表插入语句：

INSERT INTO @CultureStringResource 
VALUES
(1,'MENUITEM', 'Active Approval Documents', 'Active Approval Documents'),
(2,'MENUITEM', 'Active Approval Documents', N'審核方案'),
(3,'MENUITEM', 'Active Approval Documents', N'审核方案'),
(1,'MENUITEM', 'Active Project', 'Active Project'),
(2,'MENUITEM', 'Active Project', N'處理中'),
(3,'MENUITEM', 'Active Project', N'处理中'),
(1,'BUTTON', 'Add', 'Add'),
(2,'BUTTON', 'Add', N'新增'),
(3,'BUTTON', 'Add', N'新增')

我希望将同一项的多语言翻译合并到一行，期望结果如下：

目前我用以下查询实现需求，但新增LangId时需要手动修改SQL语句，想找能自动为每个新增LangId动态添加列的方案：

SELECT 
    c1.LanguageId, c1.Container, c1.LookupString, c1.Translation,
    (SELECT Translation 
     FROM @CultureStringResource 
     WHERE LanguageId = 1 
       AND LookupString = c1.LookupString) AS English,
    (SELECT Translation 
     FROM @CultureStringResource 
     WHERE LanguageId = 2 
       AND LookupString = c1.LookupString) AS Chinese_traditional,
    (SELECT Translation 
     FROM @CultureStringResource 
     WHERE LanguageId = 3 
       AND LookupString = c1.LookupString) AS Chinese_simplified
FROM 
    @CultureStringResource c1
WHERE 
    LanguageId = 1

解决方案 #

可以使用动态SQL实现自动生成列，无需手动修改语句，核心思路是先获取所有不同的LangId，动态拼接转置逻辑：

方法1：动态PIVOT（推荐，SQL Server 2017+） #

DECLARE @cols NVARCHAR(MAX), @query NVARCHAR(MAX)

-- 生成动态列名，可根据实际需求扩展LangId与语言名的映射
SELECT @cols = STRING_AGG(QUOTENAME(CASE LangId 
                                    WHEN 1 THEN 'English'
                                    WHEN 2 THEN 'Chinese_traditional'
                                    WHEN 3 THEN 'Chinese_simplified'
                                    ELSE 'Lang_' + CAST(LangId AS VARCHAR) -- 新增LangId默认命名
                                  END), ', ')
FROM (SELECT DISTINCT LangId FROM @CultureStringResource) AS Langs

-- 拼接动态查询语句
SET @query = N'
SELECT pvt.LangId, pvt.Container, pvt.LookupString, ' + @cols + N'
FROM (
    SELECT 
        c1.LangId, 
        c1.Container, 
        c1.LookupString,
        CASE c2.LangId 
            WHEN 1 THEN ''English''
            WHEN 2 THEN ''Chinese_traditional''
            WHEN 3 THEN ''Chinese_simplified''
            ELSE ''Lang_'' + CAST(c2.LangId AS VARCHAR)
        END AS LangName,
        c2.Translation
    FROM @CultureStringResource c1
    LEFT JOIN @CultureStringResource c2 
        ON c1.LookupString = c2.LookupString 
        AND c1.Container = c2.Container
    WHERE c1.LangId = 1 -- 以LangId=1的行作为基准行
) AS src
PIVOT (
    MAX(Translation)
    FOR LangName IN (' + @cols + N')
) AS pvt'

-- 执行动态SQL
EXEC sp_executesql @query

低版本SQL Server兼容方案（替换STRING_AGG） #

如果你的SQL Server版本低于2017，用以下方式生成列名：

SELECT @cols = STUFF((SELECT ', ' + QUOTENAME(CASE LangId 
                                                WHEN 1 THEN 'English'
                                                WHEN 2 THEN 'Chinese_traditional'
                                                WHEN 3 THEN 'Chinese_simplified'
                                                ELSE 'Lang_' + CAST(LangId AS VARCHAR)
                                              END)
                      FROM (SELECT DISTINCT LangId FROM @CultureStringResource) AS Langs
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

说明 #

1. 新增LangId时，只需在CASE语句中补充对应的语言名称映射即可；若不补充，会自动以Lang_XXX（XXX为LangId）作为列名；
2. 以LangId=1的行作为基准，关联所有同LookupString和Container的翻译，通过PIVOT转置为列。

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