SQL query subtable in columns from main query

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.

Answer

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;