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

Question

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

coalesce(c1, c2, c3, ...)

jarlh
– jarlh

2020-07-16 12:46:32 +00:00
Commented Jul 16, 2020 at 12:46 — jarlh
– jarlh, Commented Jul 16, 2020 at 12:46

user330315 · Accepted Answer · 2020-07-16 12:46:38Z

3

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)

answered Jul 16, 2020 at 12:46

user330315

Sign up to request clarification or add additional context in comments.

1 Comment

marcel f Over a year ago

Thanks, exactly what I was looking forward, did not realize there was a function specifically for that use case

Collectives™ on Stack Overflow

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

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related