Name a column with a string from the row

Question

I have the following query, which works fine:

select
  Produkt.Name,
  max(case when Substanz.ID == 1 then Inhalt.Menge end) as "Histidin",
  max(case when Substanz.ID == 2 then Inhalt.Menge end) as "Isoleucin",
  max(case when Substanz.ID == 3 then Inhalt.Menge end) as "Leucin",
  max(case when Substanz.ID == 4 then Inhalt.Menge end) as "Lysin",
  max(case when Substanz.ID == 5 then Inhalt.Menge end) as "Methionin",
  max(case when Substanz.ID == 6 then Inhalt.Menge end) as "Phenylalanin",
  max(case when Substanz.ID == 7 then Inhalt.Menge end) as "Threonin",
  max(case when Substanz.ID == 8 then Inhalt.Menge end) as "Tryptophan",
  max(case when Substanz.ID == 9 then Inhalt.Menge end) as "Tyrosin"
from Produkt
left join Inhalt on Inhalt.Produkt = Produkt.ID
left join Substanz on Inhalt.Substanz = Substanz.ID
where Substanz.essentiell = 'ja'
group by Produkt.Name;

I have read, that this is the idiomatic way to transpose a table. I am a bit annoyed that I have to specify the column names, although I have them as strings in a the table "Substanz".

Is it possible to give a column a name based on a value in a table?

Answer 1

No, in SQL it is not possible to give a column a name based on a calculated expression or a value in a table.

To obtain that you have to dynamically generate the SQL command in your programming language and then execute it with the Sqlite interface function calls, like sqlite3_exec().

See here