# Generate English sentence and omitting zeros

I try to generate an English sentence from two columns. My aim is to convert the table

``````| a | b |
|---|---|
| 0 | 1 |
| 2 | 3 |
| 4 | 0 |
``````

into the following list

``````1 B
2 A and 3 B
4 A
``````

I would like to know if there is a less verbose method than the following:

``````with tmp(a, b) as (
values
(0, 1),
(2, 3),
(4, 0)
)
select
case
when tmp.a > 0 and tmp.b > 0 then
tmp.a || ' A and ' || tmp.b || ' B'
else
case
when tmp.a > 0 then
tmp.a || ' A'
else
tmp.b || ' B'
end
end
from tmp
``````

One way you could achieve the goal is by presenting each row as a column (or unpivoting each row) and applying `string_agg` to it with `and` as a delimiter. More specifically, it could be implemented like this:

``````WITH
tmp(a, b) AS
(
VALUES
(0, 1),
(2, 3),
(4, 0)
)
SELECT
a,
b,
(
SELECT
string_agg(word, ' and ')
FROM
(
VALUES
(nullif(a, 0) || ' A'),
(nullif(b, 0) || ' B')
) AS v (word)
) AS sentence
FROM
tmp
;
``````

As you may have noticed, there is slightly more going on here than just unpivoting and aggregation. In particular, the query is also applying `nullif` to each value in order to replace a `0` with a null. That way, when a null is concatenated with a string, the result will be a null as well. And when `string_agg` comes across a null, it simply omits it.

So, for example, unpivoting the first row – `(0, 1)` – results in a column that looks like this:

``````null
1 B
``````

Aggregating this column with `string_agg` yields `1 B`, because the null is omitted and there is only one value left, so the delimiter is not used.

In contrast, when unpivoting the second row, we get:

``````2 A
3 B
``````

We have more than one value here, and when `string_agg` is applied, it uses the specified delimiter ` and ` to delimit the values, yielding the string `2 A and 3 B`.

And in the last row, we again have only one non-null value, so we get another string without an `and`.

This solution may not look much more elegant than the `CASE` solution in your question when applied to only two columns. However, it scales better in case you have more columns to concatenate, as you would only need to add more rows to the correlated query's `VALUES` constructor:

``````VALUES
(nullif(a, 0) || ' A'),
(nullif(b, 0) || ' B'),
(nullif(c, 0) || ' C'),
(nullif(d, 0) || ' D'),
...
``````

You can check this query out here at db<>fiddle.uk.