MSSQL - SELECT on data from JOIN reference

Question

SQL Server being used to run this
I'm trying to pull information that could be variable.. Could you please check it and find the error?

SELECT 
  id         = t.id
  name       = c.name
  fav_flavor = ISNULL((SELECT value FROM ce WHERE key='flavor'), '')
  fav_color  = ISNULL((SELECT value FROM ce WHERE key='color'), '')
FROM
  Transaction t
  LEFT JOIN Customer ON t.customer_id=c.id
  LEFT JOIN CustomerExtra ce ON ce.id=c.id

Of course this is a siplified query to be used in this example, but it has the scense.

So, basically, I'm not able to use that ce table referenced in the JOIN statement to get random values (in this case 'flavor' and 'color') in the main query.
Is there a way to access those values without adding the condition in the last JOIN and having to duplicate that line as many times as I need values from it? or... is it the right way?

SELECT 
  id         = t.id,
  name       = c.name,
  fav_flavor = ce1.value,
  fav_color  = ce2.value,
  cust_other = ce3.value
FROM
  Transaction t
  LEFT JOIN CustomerExtra ce1 ON ce1.id=c.id AND ce1.key='flavor'
  LEFT JOIN CustomerExtra ce2 ON ce2.id=c.id AND ce2.key='color'
  LEFT JOIN CustomerExtra ce3 ON ce3.id=c.id AND ce3.key='other'

Answer 1

Use Outer apply:

SELECT
...
,CE.*
FROM
...
OUTER APPLY (
SELECT
  Color = max(case when key = 'color' then value else '' end)
,flavor = max(case when key = 'flavor' then value else '' end)
FROM
CustomerExtra ce
Where
Ce.id = c.id
) CE

de here

Because for each customer errow you have muktiple extra key-pair values, using a aggregate function allows you just pivot this vakues to columns.