0

A sql query yields data that looks like this:

name quality magnitude
john kindness 7
john greed 2
jane kindess 3
jane greed 7
john temper 9
jane temper 4

I am wondering if there is a way to transform it into data that looks like this using SQL:

name personality
john kindess:7, greed:2, temper:9
jane kindess: 3, greed: 7, temper: 4

If yes:

  1. How do it do it?
  2. Is this an optimal/good idea?

I am on Postgres 9+

ritratt
  • 105
  • 2

1 Answers1

2

You can use string_agg() for this

select name, 
       string_agg(concat(quality, ':', magnitude), ', ') as personality
from the_table
group by name;

With a more up-to-date Postgres version, I would probably aggregate this into a JSON value which is easier to work with when you need to process the result:

select name, 
       jsonb_object_agg(quality, magnitude) as personality
from the_table
group by name;

That would return:

name | personality                             
-----+-----------------------------------------
john | {"greed": 2, "temper": 9, "kindness": 7}
jane | {"greed": 7, "temper": 4, "kindess": 3}