SQL query lists belonging to main table as columns on main table

I have a follow-up question based on the question asked in this topic: SQL query subtable in columns from main query

I’ve managed to get the following table with the query answered in the topic above:

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

Next to the result I already have I want to extend the SQL to add extra (dynamic) columns to the result based on another table.

Table_1 and table_1_lang is (ofcourse) the same:

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

table_2 (containing dynamic lists with 0-n lists)

uuid	list_code	value	order
111-etc	list_code_1	100	0
111-etc	list_code_2	50	1
222-etc	list_code_1	200	2
222-etc	list_code_2	30	0
222-etc	list_code_3	10	1

The result (next to the result stated above with the very helpful answer in previous topic) I want to create is as following: The ‘0’, ‘1’ etc in the column name from the result is the order field in the list table.

Result:

uuid	code	title-en	title-de	condition-0-list_code	condition-0-value	condition-1-list_code	condition-1-value	condition-2-list_code	condition-2-value
111-etc	123	english 123	deutch 123	list_code_1	100	list_code_2	50
222-etc	321	english 321	deutch 321	list_code_2	30	list_code_3	10	list_code_1	200

I’m trying very hard to get the result based on the query I’ve already received and thought it would just an ‘extension’ on previous query but my SQL knowledge is not so good.

To summarize what I exactly need: With the tables stated above:

• table_1
• table_1_lang
• table_2 (which can contain 0-n rows for every key/uuid in table_1)

I want to create a set as given in ‘Result’.

The column names for ‘conditions’ have to be dynamically created based on the value ‘order’ and the column a want to show in the result-column (list_code and value).

So uuid ‘111-etc’ has 2 entries in table_2 and you will see those values in row 1 of the result-table.

‘condition-2-list_code’ and ‘condition-2-value’ are empty in the result for uuid ‘111-etc’ as they are not present in table_2. For uuid ‘222-etc’ these values are filled in the result table.

Who can help me out? Thank you very much for your help in advance, will appreciate it very much.

Answer

You can pivot inside a CROSS APPLY.

For a two column pivot, it’s usually easier to just use conditional aggregation MAX(CASE WHEN

select
  t.uuid,
  t.code,
  [title-en] = len.title,
  [title-de] = lde.title,
  cond.*
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'
CROSS APPLY (
    SELECT
      [condition-0-list_code] = MAX(CASE WHEN c.[order] = 0 THEN c.list_code END),
      [condition-0-value]     = MAX(CASE WHEN c.[order] = 0 THEN c.value END),
      [condition-1-list_code] = MAX(CASE WHEN c.[order] = 1 THEN c.list_code END),
      [condition-1-value]     = MAX(CASE WHEN c.[order] = 1 THEN c.value END),
      [condition-2-list_code] = MAX(CASE WHEN c.[order] = 2 THEN c.list_code END),
      [condition-2-value]     = MAX(CASE WHEN c.[order] = 3 THEN c.value END)
    FROM table_2 c
    WHERE c.uuid = t.uuid
) cond;

You could also do this for table_1_lang