SQL – concatenate values in columns but keep only first non-null value

I have the following table in Postgresql. The first 4 records are the base data and the others were generated with the ROLLUP function.

I want to add a column “grp_1” that will display the first non-null value of the columns grp1_l1, grp2_l2 and grp2_l3

I can get to the desired result by nesting 3 “case” functions using the SQL below, but my real table has 4 groups with each 8 to 10 columns (so a lot of nested “case” function).

sql:

SELECT grp1_l1, grp1_l2, grp1_l3, case when grp1_l1 is not null then grp1_l1 else case when grp1_l2 is not null then grp1_l2 else case when grp1_l3 is not null then grp1_l3 else null end end end as grp1, value
FROM public.query_test;

Is there a better and more scalable to handle this requirement ? Any suggestions are welcome. The id will not always have 3 digits, that is just the case in my example here

Answer

Use coalesce() it’s defined as “returns the first of its arguments that is not null” – which is exactly what you want.

coalesce(grp1_l1, grp1_l2, grp1_l3)