Home > Software engineering >  SQL query lists belonging to main table as columns on main table
SQL query lists belonging to main table as columns on main table

Time:10-26

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

  • Related