Replace Row with Multiple Other Rows-CodePudding

I'm looking for a way to replace a resulting row with other values if a certain criteria is met. For Example, if the result is:

Dog
Cat
Mouse
Bear

And within Cat there is 1-2-3-4, I would like the result to instead be

Dog
Big Cat
Little Cat
Fat Cat
Orange Cat
Mouse
Bear

I've tried the below and its giving me an error of no matching signature

SELECT 
  IF(animal != 'Cat', animal, (SELECT AS STRUCT number_text_one, number_text_two, number_text_three, number_text_four from example_cat_table))

The 1-2-3-4 is just an example not to be used literally.

Is there a way this is possible?

CodePudding user response：

Consider below

select type || Animal as Animal 
from your_table
left join unnest(if(animal = 'Cat', ['Big ', 'Little ', 'Fat ', 'Orange '], [''] )) type

if applied to sample data in your question - output is

CodePudding user response：

You subsequently say there are many replacements that would be required, so I presume they'd be in a table similar to this...

parent_animal	replacement_animal	replacement_order
Cat	Big Cat	1
Cat	Little Cat	2
Cat	Fat Cat	3
Cat	Orange Cat	4
Unicorn	Mythical Beast	1
Unicorn	Narwhal	2

Then you just need a LEFT JOIN and a COALESCE(), plus one addition to the ORDER BY...

SELECT
  COALESCE(replacement.replacement_animal, original.animal)  AS animal
FROM
  original
LEFT JOIN
  replacement
    ON original.animal = replacement.parent_animal
ORDER BY
  original.ordering_column,
  replacement.replacement_order