CODEWITHSUNDEEP
sqlpostgresqlpivotcrosstab
Truica Sorin
Truica Sorin

Reputation: 539

Getting Foreign Keys as separate columns

i'm not really much of an expert when it comes to databases and i was wondering if it's possible to do something which might be a little weird

I'm having a simple table called options which has 3 columns : id(PK), user_id(FK), option

The table looks like this:

enter image description here

Is is possible to write a query that breaks the option column into multiple columns for each user_id?For example, looking at the photo it would be something like this:

user_id  |   option1  | option2  | option3  
1                5         4           2
2                7         2

I need to get the data like this because it would be easier to export it in a text file using java

Upvotes: 0

Views: 37

Answers (2)

mg.
mg.

Reputation: 8012

This is a non-answer to your question, but do you really need the options to be different columns? For example you could aggregate the options in an array:

select user_id, array_agg(option)
from options
group by user_id;

or if handling arrays with your driver is a problem and the options are really integers and not something else, you can aggregate them into a string:

select user_id, string_agg(option::text, ',')
from options
group by user_id;

Upvotes: 0

zedfoxus
zedfoxus

Reputation: 37119

Yes, you can do that with an extension called tablefunc.

Documentation https://www.postgresql.org/docs/10/tablefunc.html

Example

create table test (id int, user_id int, option int);
insert into test values (1,1,5), (2,1,4), (3,1,2,), (4,2,7), (5,2,2);

List out extensions we currently have:

# \dx
                 List of installed extensions
  Name   | Version |   Schema   |         Description          
---------+---------+------------+------------------------------
 plpgsql | 1.0     | pg_catalog | PL/pgSQL procedural language

Add tablefunc extension

# create extension tablefunc;
CREATE EXTENSION

# \dx
                                 List of installed extensions
   Name    | Version |   Schema   |                        Description                         
-----------+---------+------------+------------------------------------------------------------
 plpgsql   | 1.0     | pg_catalog | PL/pgSQL procedural language
 tablefunc | 1.0     | public     | functions that manipulate whole tables, including crosstab

Great, now the extension is available. Let's write the query

Query

select *
from crosstab('select user_id, id, option from test order by 1, 2')
          as (user_id int, option1 int, option2 int, option3 int);

Result

 user_id | option1 | option2 | option3 
---------+---------+---------+---------
       1 |       5 |       4 |       2
       2 |       7 |       2 |

Hope this helps.

Limitation

The limitation is that it does not create columns automatically. However, you can write a stored function that reads the maximum number of columns to generate and dynamically creates a query, and executes it.

Upvotes: 1

Related Questions