0

I need some tips for the Postgres pivot below, please.

I have a table like this:

+------+---+----+
| round| id| kpi|
+------+---+----+
| 0    | 1 | 0.1|
| 1    | 1 | 0.2|
| 0    | 2 | 0.5|
| 1    | 2 | 0.4|
+------+---+----+

The number of Ids is unknown.

I need to convert the id column into multiple columns (same amount of different ids), with KPI value as their values and in the new table we keep the rounds like in the first table.

+------+----+----+
| round| id1| id2|
+------+----+----+
| 0    | 0.1| 0.5|
| 1    | 0.2| 0.4|
+------+----+----+

Is it possible to do this in SQL? How to do that?

Thaise
  • 1,043
  • 3
  • 16
  • 28
  • 1
    Not possible. One of the fundamental restrictions of the SQL language is that the number, name and data type of all columns must be know to the database _before_ running the query. Displaying such a table is much better done in your application, SQL was not designed for this. –  Jul 07 '21 at 12:44
  • @a_horse_with_no_name: To be precise: not *before* but *at* the time of running the query - which allows for polymorphic functions. – Erwin Brandstetter Jul 07 '21 at 13:52
  • Is there a known *maximum* number of distinct IDs? Or is there a meta-information in the database (like a target table) that would give us the target columns? Else, you need a two-step workflow: 1.: Construct the query 2. Execute it. Not really a problem, either ... – Erwin Brandstetter Jul 07 '21 at 14:03

1 Answers1

0

It´s possible, check this question

This other is a pivot that I did, also with an unknown number of columns, maybe it can help you too: Advanced convert rows to columns (pivot) in SQL Server

Chesco1997
  • 73
  • 1
  • 8