Marco Marco - 6 months ago 14
SQL Question

Is there a way to break out a row if it meets a certain value?

For example, let's say there's a table like this:

ID Food
-- ----
1 Fruit


So whenever it says Fruit, I want to break it out into 3 rows:

ID Food
-- ----
1 Apple
1 Banana
1 Orange


Any tips?

Answer

Hmmm . . . You could do:

select t.id, coalesce(a.alt, t.food) as food
from t outer apply
     (select alt
      from (values ('Apple'), ('Banana'), ('Orange')) v(alt)
      where t.food = 'Fruit'
     ) a;