Create a field with conditional values based on other fields in SQL

Question

I have a table like this:

ID	Revenue_Tom	Revenue_John	Revenue_Lisa
aaa	0	257	138
aab	376	0	0

And I need to create a table where the ID field is the value of the old ID + the name of the field where it has no 0 values. If an ID has more than 2 fields with non-zero values, I need to create 2 new IDs separately, and I need the Revenue field to be the value of the old revenue that has been considerated in each case.

That would be the expected output (It's a bit hard to explain, I think the expected output is more self-explicative):

ID2	Revenue
aaa_John	257
aaa_Lisa	138
aab_Tom	376

(I'm doing this in DBT which uses SQL+Jinja)

Answer 1

If you create 3 queries and join the with union all you will have your result.

SELECT CONCAT(ID,'_Tom') ID2
   Revenue_Tom Revenue
FROM table_name
WHERE Revenue_Tom > 0
   UNION ALL
SELECT CONCAT(ID,'_John') ID2
   Revenue_John
FROM table_name
WHERE Revenue_John > 0
   UNION ALL
SELECT CONCAT(ID,'_Lisa') ID2
   Revenue_Lisa
FROM table_name
WHERE Revenue_Lisa > 0;