Thank you for the help in advance!

Say I have a table like this (it is basically gonna be used for translation of our App's UI):

Test table:

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'新增')

I would like to query against this table, and sort of group the translation in 1 line.

The desired result should look like following.

I can achieve this by running this query:

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

By using this query, I would need to change the query every time there is a new language (lang_id) added to the table,

Is there any better way to do this so it will automatically select 1 more column for every distinct lang_id inserted?

CodePudding user response：

It will make things easier if you have a Language table

create table Language
(
    LanguageId int,
    Language nvarchar(100)
);

insert into Language values
(1, 'English'),
(2, 'Chinese_tranditional'),
(3, 'Chinese_simplified');

With that, you can form the dynamic query and use sp_executesql to execute it

declare @sql nvarchar(max),
        @cols nvarchar(max)
        
select @cols = string_agg(col, ',')
               within group (order by LanguageId)
from   Language l
       cross apply
       (
           select col = concat(char(13), 
                              'MAX (case when c.LanguageId = ', 
                              l.LanguageId, 
                              ' then Translation end) as ', 
                              Language)
       ) c

select @sql = concat('SELECT c.Container, c.LookupString,',
                     @cols, char(13),
                     'FROM CultureStringResource c ', char(13),
                     'GROUP BY c.Container, c.LookupString;')

print @sql;

exec sp_executesql @sql;

The output of @sql is as follow

SELECT c.Container, c.LookupString,
       MAX (case when c.LanguageId = 1 then Translation end) as English,
       MAX (case when c.LanguageId = 2 then Translation end) as Chinese_tranditional,
       MAX (case when c.LanguageId = 3 then Translation end) as Chinese_simplified
FROM   CultureStringResource c 
GROUP BY c.Container, c.LookupString;