In short: I would like to use this input:

```
+---+---+---+
| x | y | z |
+---+---+---+
| 1 | 1 | a |
| 1 | 2 | b |
| 1 | 3 | c |
| 2 | 1 | d |
| 2 | 2 | e |
| 2 | 3 | f |
| 3 | 1 | g |
| 3 | 2 | h |
| 3 | 2 | i |
| . | . | . |
| n | . | .
+---+---+---+
```

to generate this output:

```
+---+---------+---------+---------+---------+
| y | z (x=1) | z (x=2) | z (x=3) | z (x=n) |
+---+---------+---------+---------+---------+
| 1 | a       | d       | g       |    .    |
| 2 | b       | e       | h       |    .    |
| 3 | c       | f       | i       |    .    |
+---+---------+---------+---------+---------+
```

Table sample:
```sql
CREATE TABLE "public"."data" (
    "x" text NOT NULL,
    "y" text NOT NULL,
    "z" text NOT NULL,
);
```

- The goal is to generate the output, in the most efficient way possible.
- max(x) will increase over time (->n)
- max(y) should remain constant but may increase by ~10%
- dynamic creation of z(x) columns & names


So far I have the following:

```sql
select * from crosstab('select y, x, z from data order by 1,2')
as ct (y varchar, x1z varchar, x2z varchar, x3z varchar, 
                  x4z varchar, x5z varchar, x6z varchar)
;
```

 which seems to work well (so far):

```
+----+-----+-----+-----+-----+-----+-----+
| y  | x1z | x2z | x3z | x4z | x5z | x6z |
+----+-----+-----+-----+-----+-----+-----+
| 10 | fo  | ob  | ar  | fo  | ob  | ar  |
| 20 | ob  | ar  | fo  | ob  | ar  | fo  |
| 30 | ar  | fo  | ob  | ar  | fo  | ob  |
+----+-----+-----+-----+-----+-----+-----+
```

In the previous SQL snippet, I manually defined the static column names.
These should based on x values & hence dynamic. 

Thoughts?

---

Another example to add clarity

- using the same crosstab SQL snippet, with arbitrarily defined column names
- these column names should be dynamically defined, in this example you can say: 'worldcup'+'year'
- in the previous case only 'x' is required, as is

```sql
CREATE TABLE world_cup(
	year varchar(5), 
	game varchar(5), 
	score varchar(5))
	;

-- insert values ...

select * from crosstab('select game, year, score from world_cup order by 1,2')
as ct (game varchar, WorldCup17 varchar, WorldCup18 varchar,
  WorldCup19 varchar, WorldCup20 varchar, WorldCup21 varchar, WorldCup22 varchar)
	
```

```
+-------+------------+------------+------------+------------+------------+------------+
| match | worldcup17 | worldcup18 |	worldcup19 | worldcup20 | worldcup21 | worldcup22 |
+-------+------------+------------+------------+------------+------------+------------+
| DE_FR	|     2-2    |     1-1    |     0-0    |     3-2    |     0-2    |     1-2    |
| EN_DE	|     2-0    |     0-2    |     2-1    |     0-0    |     3-0    |     0-0    |
| ES_FR	|     0-1    |     0-0    |     1-5    |     0-5	|     1-1    |     3-1    |
+-------+------------+------------+------------+------------+------------+------------+
```

Playground:
```
https://dbfiddle.uk/-zHbXWwP
````