Multiple Column value in a single row

Phoenix Forum Questions / Help
,
1

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
----------------------------------------------------------
2

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 - https://www.postgresql.org/message-id/20080202160436.GA32408@a-kretschmer.de
2 - more complete description - https://lerner.co.il/2014/05/23/turning-postgresql-rows-arrays-array/ (assuming your data is in Postgres).

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

3

A crosstab is what you want. See https://stackoverflow.com/questions/49051959/postgresql-crosstab-simple-example

2 Likes
4

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 https://stackoverflow.com/questions/34502696/postgres-crosstab-dynamic-number-of-columns), so it does have significant limitations which may or not may be ok depending on the OP’s use case.

1 Like
5

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.

6

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 https://hexdocs.pm/postgrex/Postgrex.Result.html) that you can then transform into your required output.

1 Like
7

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
8

Nice feature

1 Like