How can I separate column values in to column names?

Question

Im trying to make a table with dynamic columns.

I have this table. This is just simplified, in other instances these may have more values instead of 3.

         name         
----------------------
 Fall
 Medication 
 Wander
(3 rows)

I am trying to get this result. I need to separate the values into columns.

  Fall  | Medication | Wander 
--------+------------+--------
(0 rows)

so this query will return the first value "Fall". what exactly you want. you can use a[2] and a[3] to get the other values — lat long, Jan 04 '18 at 07:43
I dont want it to return the value. would want it to return the value as a column name. — kimdasuncion12, Jan 04 '18 at 08:34
you can add commas to the value like ''' || a[1]|| '''. it will return 'Fall' — lat long, Jan 04 '18 at 10:16
Yes I know Ive tried that but i cant use these "a[1]" because there are instances that there are more values. These should be dynamic not constant — kimdasuncion12, Jan 04 '18 at 10:32

score 0 · Answer 1 · answered Jan 04 '18 at 07:41

0

You need to PIVOT the table. Unfortunately MySQL (unlike Oracle http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html) doesn't have a PIVOT operator, but there are workarounds it seems. MySQL pivot table

answered Jan 04 '18 at 07:41

leonardseymore

533
5
20

The question is about Postgres, not MySQL (but Postgres doesn't have pivot either) – Jan 04 '18 at 09:03
Looks like @prinsarian might have the answer you're looking for. crosstab() does look like the function you're looking for – leonardseymore Jan 04 '18 at 09:15
@leonardseymore tried crosstab but I still have to name the columns for that. So this is not the answer – kimdasuncion12 Jan 04 '18 at 10:33

score 0 · Answer 2 · edited Jan 04 '18 at 09:04

0

You might try one of the crosstab() functions in newer PostgreSQL. See https://www.postgresql.org/docs/current/static/tablefunc.html (F.35.1.2 and F.35.1.4). It allowes you to specify a query that has row-names (those create rows), categories (that create columns) and values (that populate the inside part of the table.

The variant with 2 queries can be especially useful since it allows you to specify the columns you want to use with a seperate query.

edited Jan 04 '18 at 09:04

answered Jan 04 '18 at 07:50

prinsarian

31
3

(i'm assuming your question is about the PostgreSQL database and a fairly recent version) – prinsarian Jan 04 '18 at 07:52
Yes but you will have to declare the column names. SELECT * FROM crosstab('...') AS ct(row_name text, category_1 text, category_2 text); – kimdasuncion12 Jan 04 '18 at 07:55

How can I separate column values in to column names?

2 Answers2