SQL server Select statement, dynamically select 1 more column for each new distinct value in a column

Question

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

LangId	Container	LookupString	Translation
1	MENUITEM	Active Approval Documents	Active Approval Documents
2	MENUITEM	Active Approval Documents	審核方案
3	MENUITEM	Active Approval Documents	审核方案
1	MENUITEM	Active Project	Active Project
2	MENUITEM	Active Project	處理中
3	MENUITEM	Active Project	处理中
1	BUTTON	Add	Add
2	BUTTON	Add	新增
3	BUTTON	Add	新增

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.

LangId	Container	LookupString	English	Chinese_tranditional	Chinese_simplified
1	MENUITEM	Active Approval Documents	Active Approval Documents	審核方案	审核方案
1	MENUITEM	Active Project	Active Project	處理中	处理中
1	BUTTON	Add	Add	新增	新增

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?

You will need to use Dynamic SQL [sp_executesql](https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-executesql-transact-sql?view=sql-server-ver16) to perform the pivoting — Squirrel, Aug 23 '22 at 06:03
Also have a look at https://stackoverflow.com/questions/10986031/sql-dynamic-view-with-column-names-based-on-column-values-in-source-table — Mihe, Aug 23 '22 at 06:03

score 1 · Accepted Answer · answered Aug 23 '22 at 06:41

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;

SQL server Select statement, dynamically select 1 more column for each new distinct value in a column

1 Answers1