CODEWITHSUNDEEP
sqlstringpostgresqlpivotaggregate-functions
Enrique
Enrique

Reputation: 93

PostgreSQL - How to concatenate row values after a conditional

I have a table in postgres:

| PersonID |Description | Value |
|----------|-------------|-------|
|     1    | Name        | Jane  |
|     1    | Last name   | Doe   |
|     1    | Age         | 23    |
|     1    | Country     | USA   |
|     2    | Name        | Steve |
|     2    | Last name   | Jobs  |
|     2    | Age         | 40    |
|     2    | Country     | India |
|     1    | Height      | 1.80  |
|     1    | Weight      | 80    |
|     2    | Height      | 1.72  |
|     2    | Weight      | 79    |

and I want this (obs: Ref code = height+weight):

| Name    | Last_name | Age | Country | Ref. code |
|---------|-----------|-----|---------|-----------|
| Jane    | Doe       | 23  | USA     | 1.8080    |
| Steve   | Jobs      | 40  | India   | 1.7279    |

I already has this script, but I don't have the concat part for the ref code column:

select person_id,
    max(case when description = 'Name' then value end) as name,
    max(case when description = 'Last name' then value end) as last_name,
    max(case when description = 'Age' then value end) as age,
    max(case when description = 'Country' then value end) as country
from mytable
group by person_id

Please help! And thanks in advance

Upvotes: 2

Views: 2074

Answers (2)

Barbaros Özhan
Barbaros Özhan

Reputation: 65278

You just need to concatenate those columns with double pipe characters such as

select q.*,
       name||last_name||country as "Ref. code"
  from
  (
   select person_id,
          max(case when description = 'Name' then value end) as name,
          max(case when description = 'Last name' then value end) as last_name,
          max(case when description = 'Age' then value end) as age,
          max(case when description = 'Country' then value end) as country
     from mytable
    group by person_id
  ) q

Update : In your current case, you can apply string_agg() function to the single value column with conditional(whenever descriptions are either Height or Weight) such as

select person_id,
       max(case when description = 'Name' then value end) as name,
       max(case when description = 'Last name' then value end) as last_name,
       max(case when description = 'Age' then value end) as age,
       max(case when description = 'Country' then value end) as country,
       string_agg(case when Description in ('Height','Weight') then value end,'') as "Ref. code"
  from mytable
 group by person_id

Demo

Upvotes: 3

GMB
GMB

Reputation: 222482

You can use string aggregation. Also, in Postgrs, we can simplify the aggregate expressions with filter():

select person_id,
    max(value) filter(where description = 'Name'     ) as name,
    max(value) filter(where description = 'Last name') as last_name,
    max(value) filter(where description = 'Age'      ) as age,
    max(value) filter(where description = 'Country'  ) as age,
    string_agg(value, '-' order by description) filter(where description in ('Name', 'Last name', 'Country')) as ref_code
from mytable
group by person_id

This gives you the flexibility of adding any description that you like to the ref code, even if it is not returned by the other aggregate functions.

I would recommend adding a separator between the ref parts, so it is clearer how it is composed. I used '-' (you can change that to '' if you don't want a separator).

Note that this orders values by their description. If you really want to fine-tune the ordering, then you can use a case expression in the order by clause of the aggregation function:

string_agg(
    value, 
    '-' 
    order by case description
        when 'Name'      then 1
        when 'Last name' then 2
        when, 'Country'  then 3
    end
) filter(where description in ('Name', 'Last name', 'Country')) as ref_code

Upvotes: 1

Related Questions