Multiple Column value in a single row

If I have a table like this

Id    |   Status
1     |  5
1     |  6
1     |  7

How can i get the result like below

ID    |       col1       |         col2     |       col3
1     |         5        |           6      |        7

Hi @theazharul, welcome to the forums…

There are lots of answers to that question depending on where you need the flattened information, where it comes from, whether you have a fixed number of expected statuses, etc, etc

Here are a couple of pure Postgres implementations:
1 - simple -
2 - more complete description - (assuming your data is in Postgres).

Maybe if you provide a little more context we could offer a more appropriate solution.

A crosstab is what you want. See


Crosstab requires a “value” field that is transformed into the output column name (e.g. “Status” in the linked SO article), and also has a fixed number of output columns (see, so it does have significant limitations which may or not may be ok depending on the OP’s use case.

1 Like

Thank you. I am new in Phoenix framework. Trying to build a json API. My data is in Postgresql. I posted it to get idea. My table is like below

ID   | sector              |   amount
1    | tax                 |   20
2    | purchase_wallet     |   18
3    | IT_charge           |   2

I need to put “tax”, “purchase_wallet”, “IT_charge” as column name.
May be crosstab is what I need. But how can I implement it in Phoenix Framework with repo.

If you can get the query going in Postgres you can run it at get an array of rows back using Ecto.Adapters.SQL.query!(MyApp.Repo, query_string) where MyApp.Repo is the replaced by your Repo module as per normal Ecto setup, and query_string is the query you got working directly in Postgres. That will return a results structure (see that you can then transform into your required output.

1 Like

Might be interested in PostgreSQL’s json_object_agg as well, can take the keys and the values and make a json object out of them so you have the mappings as well.

1 Like

Nice feature

1 Like