coalesce works only once

Question

I have a table with redundant values in the first two columns:

sqlite> select * from amino where "lebensmittel" = 'Walnüsse';
Lebensmittel  Protein pro 100g  Histidin  Isoleucin  Leucin   Lysin    Methionin  Cystein  Phenylalanin  Tyrosin  Threonin  Tryptophan  Valin 
------------  ----------------  --------  ---------  -------  -------  ---------  -------  ------------  -------  --------  ----------  ------
Walnüsse      15,23 g           391 mg                                                                                                        
Walnüsse      15,23 g                     625 mg                                                                                              
Walnüsse      15,23 g                                1170 mg                                                                                  
Walnüsse      15,23 g                                         0,424 g                                                                         
Walnüsse      15,23 g                                                  236 mg                                                                 
Walnüsse      15,23 g                                                             208 mg                                                      
Walnüsse      15,23 g                                                                      711 mg                                             
Walnüsse      15,23 g                                                                                    406 mg                               
Walnüsse      15,23 g                                                                                             596 mg                      
Walnüsse      15,23 g                                                                                                       170 mg            
Walnüsse      15,23 g                                                                                                                   753 mg

I tried to merge the columns with coalesce, but it works only for the first column:

sqlite> select
  "Lebensmittel",
  "Protein pro 100g",
  coalesce("Histidin", null) as "Histidin",
  coalesce("Isoleucin", null) as "Isoleucin",
  coalesce("Leucin", null) as "Leucin",
  coalesce("Lysin", null) as "Lysin",
  coalesce("Methionin", null) as "Methionin",
  coalesce("Cystein", null) as "Cystein",
  coalesce("Phenylalanin", null) as "Phenylalanin",
  coalesce("Tyrosin", null) as "Tyrosin",
  coalesce("Threonin", null) as "Threonin",
  coalesce("Tryptophan", null) as "Tryptophan",
  coalesce("Valin", null) as "Valin"
from amino
where Lebensmittel = 'Walnüsse'
group by "Lebensmittel", "Protein pro 100g";
Lebensmittel  Protein pro 100g  Histidin  Isoleucin  Leucin  Lysin  Methionin  Cystein  Phenylalanin  Tyrosin  Threonin  Tryptophan  Valin
------------  ----------------  --------  ---------  ------  -----  ---------  -------  ------------  -------  --------  ----------  -----
Walnüsse      15,23 g           391 mg                                                                                                    

Can anybody explain what is wrong with my query?

Answer 1

I seems that coalesce can not be used as an aggregate function. But group_concat works fine:

select
  "Lebensmittel",
  "Protein pro 100g",
  group_concat("Histidin", '') as "Histidin",
  group_concat("Isoleucin", '') as "Isoleucin",
  group_concat("Leucin", '') as "Leucin",
  group_concat("Lysin", '') as "Lysin",
  group_concat("Methionin", '') as "Methionin",
  group_concat("Cystein", '') as "Cystein",
  group_concat("Phenylalanin", '') as "Phenylalanin",
  group_concat("Tyrosin", '') as "Tyrosin",
  group_concat("Threonin", '') as "Threonin",
  group_concat("Tryptophan", '') as "Tryptophan",
  group_concat("Valin", '') as "Valin"
from amino
where Lebensmittel = 'Walnüsse'
group by "Lebensmittel", "Protein pro 100g";