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.
CodePudding user response：
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