SQL query subtable in columns from main query-CodePudding

I have 2 tables with a main table with a unique UUID as index and a sub-table containing the languages of 1 row of the main table.

E.g. table_1

uuid	code
111-etc	123
222-etc	321

table_1_lang

uuid	lang_code	title
111-etc	en	english 123
111-etc	de	deutch 123
222-etc	en	english 321
222-etc	de	deutch 321

I want to create a query that has a result of 1 row per main table (table_1) and add extra columns for each lang_code. I've managed to create something close to the end result with hard-coded lang_code's but the _lang table can be dynamic (extra languages) so this is not what I want. The result must be like:

Result:

uuid	code	title-en	title-de
111-etc	123	english 123	deutch 123
222-etc	321	english 321	deutch 321

Tables

Edit: As image because the layout of the tables seemed to be screwed up

Thanks for the help.

CodePudding user response：

LEFT JOIN the table_1_lang table once for each language:

select t.uuid, t.code, len.title, lde.title,
from table_1 t
left join table_1_lang len on t.uuid = len.uuid and len.lang_code = 'en'
left join table_1_lang lde on t.uuid = lde.uuid and lde.lang_code = 'de'

CodePudding user response：

First, you need to convert your defined row into a column name using stuff and then using pivot to fulfill your requirement.

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

select @cols =  STUFF((SELECT ','   QUOTENAME('title-' lang_code) 
                    from table_1_lang
                    group by lang_code                    
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')
        print @cols
set @query = N'SELECT uuid,code, '   @cols   N' from 
             (
                select t1.uuid,t1.code,concat(''title-'',t2.lang_code) as lang_code,t2.title from table_1 t1
                inner join table_1_lang t2 on t1.uuid = t2.uuid
            ) x
            pivot 
            (
                max(title)
                for lang_code in ('   @cols   N')
            ) p '

print @query
exec sp_executesql @query;