How to transform a table in PostgreSQL?

Question

PostgreSQL

I have a table like this:

+-----------+----------+------------------------+
| week_no   | ticker   | ratio                  |
|-----------+----------+------------------------|
| 39.0      | PBRT     | 0.33333333333333333333 |
| 38.0      | ADM      | 0.50000000000000000000 |
| 40.0      | TSCO     | 1.00000000000000000000 |
| 39.0      | AAL      | 0.09090909090909090909 |
+-----------+----------+------------------------+

I need to transform the table into a similar format:

+-----------+------------------------+------------------------+------------------------+------------------------+
| week_no   | adm                    | pbrt                   | aal                    | tsco                   |
|-----------+------------------------+------------------------+------------------------+------------------------|
| 38.0      | 0.50000000000000000000 | <null>                 | <null>                 | <null>                 |
| 39.0      | <null>                 | 0.33333333333333333333 | 0.09090909090909090909 | <null>                 |
| 40.0      | <null>                 | <null>                 | <null>                 | 1.00000000000000000000 |
+-----------+------------------------+------------------------+------------------------+------------------------+

So that each ticker becomes a separate column, and weeks are rows.

I am using a script like this:

SELECT *   
       FROM crosstab(   
           'select week_no, ticker, ratio   
           from rejected_ratio_per_ticker_and_week_for_crosstab   
           order by 1,2')   
       AS ct(week_no double precision, ADM numeric, PBRT numeric, AAL numeric, TSCO numeric);

But in the output I get this:

+-----------+------------------------+------------------------+--------+--------+
| week_no   | adm                    | pbrt                   | aal    | tsco   |
|-----------+------------------------+------------------------+--------+--------|
| 38.0      | 0.50000000000000000000 | <null>                 | <null> | <null> |
| 39.0      | 0.09090909090909090909 | 0.33333333333333333333 | <null> | <null> |
| 40.0      | 1.00000000000000000000 | <null>                 | <null> | <null> |
+-----------+------------------------+------------------------+--------+--------+

What should I do? This is my first time using crosstab and I don't quite understand the logic of its ORDER BY.

Thanks for the help!

Answer 1

This is not a transformation, but the preparation of a suitable sample.
You do not change the structure of the table, only the form of obtaining data?

select t1.week_no,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'ADM') adm,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'PBRT') pbrt,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'ALL') aal,
(select max(t2.ratio) from table t2 where t2.week_no = t1.week_no and t2.ticker = t1.ticker and t1.ticker = 'TSCO') tsco
from table t1

PS: max is taken to display a single property value and there was no error if there are duplicate records for one week, you can use Limit in subqueries or another criterion, for example, row_number.
PPS: You can make a view based on this selection if you need to use the result as a table.