getting concatenated desc from a table of 3 different columns of another table

Question

I am trying to get a single description column from a reference table in PostgreSQL using 3 id columns as a concatenated value.

I have a id Table as below:

+-----+-----+-----+
| id1 | id2 | id3 |
+-----+-----+-----+
|   1 |   2 |   3 |
|   4 |   6 |   5 |
+-----+-----+-----+

and Reference Table;

+----+----------+
| id |   desc   |
+----+----------+
|  1 | apple    |
|  2 | boy      |
|  3 | cat      |
|  4 | dog      |
|  5 | elephant |
|  6 | Flight   |
+----+----------+

The Desired expected output is as below

I just have to concat a "/M" in the end additionally.

I don't have to add /M if id2 and id3 both are null

+-----------------------+
|         desc          |
+-----------------------+
| apple+boy+cat/M       |
| dog+Flight+Elephant/M |
+-----------------------+

Possible duplicate of https://stackoverflow.com/questions/43870/how-to-concatenate-strings-of-a-string-field-in-a-postgresql-group-by-query — Sami Ahmed Siddiqui, Apr 29 '19 at 06:56

score 4 · Accepted Answer · 2019-04-29T11:55:36.543

4

You can use string_agg() to concatenate all rows with a single expression. Something like:

select (select string_agg(r.descr, '+' order by r.id)||
                case when count(r.descr) > 1 then '/M' else '' end
        from ref r 
        where id in (i.id1, i.id2, id3)) as descr
from id_table i;

Online example: https://rextester.com/KVCGLD44632

The above sorts the descriptions by the ID value. If you need to preserve the order of the columns in the "id table", you could use something like this:

select (select string_agg(r.descr, '+' order by t.idx)||
               case when count(r.descr) > 1 then '/M' else '' end
        from ref r 
          join (values (i.id1, 1), (i.id2, 2), (i.id3, 3)) as t(id, idx) 
            on t.id = r.id
       ) as descr
from id_table i;

Note that desc is a reserved keyword, you should not use it as a column name. That's why I used descr in my example.

edited Apr 29 '19 at 11:55

answered Apr 29 '19 at 06:53

Can u kindly help me for adding isnotnull condition for the output if the column id or descr is null if in case. – John Humanyun Apr 29 '19 at 07:57
@JohnHumanyun: both queries will properly deal with NULL values in the `id_table` or the `descr` column – Apr 29 '19 at 11:06
https://rextester.com/EOX88674 Please Check Here in the second row both the 2nd and 3rd id is empty then I shouldn't be adding .M. – John Humanyun Apr 29 '19 at 11:11
@JohnHumanyun: well that is something different than "dealing with NULL values". You should have mentioned that the `/M` suffix should only be added if the there are at least two elements to join – Apr 29 '19 at 11:14
@JohnHumanyun: what if only `id1` is null and `id2` and ìd3` are not? Or only `id2` is null? Or only `id3` or `id1` and `id3`? – Apr 29 '19 at 11:18
id1 cannot be null. That’s the rule. – John Humanyun Apr 29 '19 at 11:52
That’s the primaryid and rest two are secondary and tertiary – John Humanyun Apr 29 '19 at 11:52
1

@JohnHumanyun: see my edit, you can add a CASE expression that checks for the number of descriptions – Apr 29 '19 at 11:56

getting concatenated desc from a table of 3 different columns of another table

1 Answers1