Complex concatenation of columns into one column

Question

I have this code:

SELECT DISTINCT
CASE
    WHEN abc > def THEN 'abc is larger than def'::text
    ELSE NULL::text
END AS case1,
CASE
    WHEN abc < ghi THEN 'def is larger than abc'::text
    ELSE NULL::text
END AS case2,
CASE 
    WHEN abc > jkl THEN 'abc is larger than jkl'::text
    ELSE NULL::text
END AS case3,

This looks like this:

-------------------------------------------------------------------------------------
|              case1          |           case2          |          case3           |
-------------------------------------------------------------------------------------
|    'abc is larger than def' | 'def is larger than abc' | 'abc is larger than jkl' |
-------------------------------------------------------------------------------------

What I want to do is to create a column called 'casesCombined', than would combine 'then's'. For example, if top 2 cases are true, then column 'casesCombined' would be '"abc is larger than def", "def is larger than abc"'.

Like this:

-----------------------------------------------------
|                  casesCombined                    |
-----------------------------------------------------
| 'abc is larger than def', 'def is larger than abc'|
-----------------------------------------------------

Tried many ways, not able to figure out. I also don't need those 'case1', 'case2'...

score 0 · Accepted Answer · edited May 23 '17 at 10:27

SELECT concat_ws(', ', CASE WHEN abc > def THEN 'abc is larger than def' END
                     , CASE WHEN abc < ghi THEN 'def is larger than abc' END
                     , CASE WHEN abc > jkl THEN 'abc is larger than jkl' END
                ) AS cases_combined
FROM tbl;

CASE defaults to NULL when there is not ELSE clause.
concat_ws() ignores NULL values.

I did not add extra single-quotes since I assume you did not actually want those in the result. Just add if you do:

Insert text with single quotes in PostgreSQL

Complex concatenation of columns into one column

1 Answers1