Dynamic column alias from another column value in SELECT

user_7387126 0
Viewed 1159

I was wondering if there a way, in a SELECT statement on Postgres, to alias a column with the value of another column in the same data set.

Given this table:

id key value
1 a d
2 a e
3 b f

This would be the result:

id a b
1 d NULL
2 e NULL
3 NULL f

Where for each instance the name of the column is determined from the value of key while the value is the value of the column value, not knowing what kind of values will be provided by the column key.

This is a possible (not working) query:

SELECT "id", "value" AS "t"."key" FROM testTable as t;
crosstab select postgresql sql
2 Answers

One way to achieve pivot in Postgres is using CASE :

select id,
  max(case when (key='a') then value else NULL end) as a,
  max(case when (key='b') then value else NULL end) as b
  FROM TestTable
 group by id
 order by id;
user_24557310

It seems that there is no way to create the column alias dynamically without knowing the values since the beginning. As many commented the only way to achieve this kind of "table re-mapping" is to use the crosstab function.

Crosstab function summary

This function takes 2 arguments:

  • The first one is a SQL statement that must return 3 columns:
    • The first column contains the values identifying each instance and that must be grouped in order to get the final result.
    • The second column contains the values that are used as categories in the final pivot table: each value will create a separate column.
    • The third column contains the values used to compile the new columns formed: for each category this column has the value of the instance that had the category value in the original table.
  • The second argument is not mandatory and is a SQL statement that returns the distinct values the function should use as categories.

Example

In the example above we must pass a query to crosstab that:

  • Returns as the first column the identifier of each final instance (in this case id)
  • As second column the values used as categories (all values in key)
  • As third column the values used to fill the categories (all values in value)

So the final query should be:

select * from crosstab(
    'select "id", "key", "value" from testTable order by 1, 2;',
    'select distinct "key" from testTable order by 1;'
) as result ("id" int8, "a" text, "b" text);

Since the crosstab function requires a column definition for the final pivot table, there is no way to determine the column alias dynamically.

Dynamically infer column names with client

A possible way to do that, with a PostgreSQL client, is to launch the second query we passed as argument to crosstab in order to retrieve the final columns and then infer the final crosstab query.

As an example, with pseudo-javascript:

const client;

const aliases = client.query(`select distinct "key" from testTable order by 1;`);

const finalTable = client.query(`select * from crosstab(
    'select "id", "key", "value" from testTable order by 1, 2;',
    'select distinct "key" from testTable order by 1;'
) as result ("id" int8, ${aliases.map(v => v + ' data_type').join(',')});`)

Useful articles

https://learnsql.com/blog/creating-pivot-tables-in-postgresql-using-the-crosstab-function/

user_73871260
Related