1

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

1 Answer 1

2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.