0

For my example I have two tables with table 1 being the main table 2 referencing metadata to table one. See the structure of those tables below.

Table 1:

Table 1

Table 2:

Table 2

The following condition is given:

Table2(data_id) REFERENCES Table1 ON Table1(id);

The metadata references have different names within the "name" column. Those names are not equal for all the metadata, but they repeat very often (overall it is not more than 25-30 names). Now - with a single SQL query - I want to export a table like this:

ID#created#foerderung#company#firstname#lastname#email#...

... and so on.

So the values from the name column are becoming a column name and the values from the value column are becoming the content of that column name. If there is no certain column name like this, the value in the result table should be null or empty.

SELECT DISTINCT name FROM table2;

Gives me exactly the columns I want to have for the final result. But how can I convert this result into columns and select the data into it as intended?

j08691
  • 204,283
  • 31
  • 260
  • 272
Blackbam
  • 17,496
  • 26
  • 97
  • 150
  • 1
    This 'dynamic attributes' model is called EAV, and it's many challenges have been discussed extensively, including your question. See https://stackoverflow.com/search?q=EAV I can only suggest that if you have some control over the design of the database, seriously reconsider this so called model. It will be a source of endless grief. – SQLRaptor Jul 05 '19 at 16:11
  • @SQLRaptor Ok thanks for pointing me into the correct direction. Unfortunatly I do not have any power over changing the database design as this is the database design of a third party extension. – Blackbam Jul 05 '19 at 16:13

0 Answers0