How to get values to dense table with columns of categories using PostgreSQL (crosstab)?

Question

I have this toy example which gives me sparse table of values separated in their different categories. I would want to have dense matrix, where all columns are individually ordered.

drop table if exists temp_table;
create temp table temp_table(
    rowid int
    , category text
    , score int
    );
insert into temp_table values (0, 'cat1', 10);
insert into temp_table values (1, 'cat2', 21);
insert into temp_table values (2, 'cat3', 32);
insert into temp_table values (3, 'cat2', 23);
insert into temp_table values (4, 'cat2', 24);
insert into temp_table values (5, 'cat3', 35);
insert into temp_table values (6, 'cat1', 16);
insert into temp_table values (7, 'cat1', 17);
insert into temp_table values (8, 'cat2', 28);
insert into temp_table values (9, 'cat2', 29);

Which gives this temporary table:

rowid	category	score
0	cat1	10
1	cat2	21
2	cat3	32
3	cat2	23
4	cat2	24
5	cat3	35
6	cat1	16
7	cat1	17
8	cat2	28
9	cat2	29

Then ordering score values to different columns based on their category:

select "cat1", "cat2", "cat3"
from crosstab(
    $$ select rowid, category, score from temp_table $$ -- as source_sql
    , $$ select distinct category from temp_table order by category $$ -- as category_sql
 ) as (rowid int, "cat1" int, "cat2" int, "cat3" int)
 

That outputs:

cat1	cat2	cat3
10
	21
		32
	23
	24
		35
16
17
	28
	29

But I would want the result of the query to be dense, like:

cat1	cat2	cat3
10	21	32
16	23	35
17	24
	28
	29

Maybe PostgreSQL's crosstab is not even right tool to do this, but that comes to my mind first as it produces that sparse table close to the result I would need.

Answer 1

This should work for the exact given example data and expected output.

select max(cat1), max(cat2), max(cat3) 
from crosstab(
$$ select rank() over(partition by category order by rowid) as ranking, 
  rowid, 
  category, 
  score 
from temp_table 
order by rowid, category asc$$ -- as source_sql
, $$ select distinct category 
from temp_table 
order by category $$ -- as category_sql
  ) as (ranking int, rowid int, "cat1" int, "cat2" int, "cat3" int) 
group by ranking 
order by ranking asc

You can test the solution here - https://dbfiddle.uk/?rdbms=postgres_14&fiddle=f198e40a18a282cc0d65fa6ecdf797cb

Edit: Improvements made to your query to arrive at the solution:

1. In the source SQL query, I have ranked the category values based on the rowid order, which helps "determining" the order of the expected values, as per your requirement.

select rank() over(partition by category order by rowid) as ranking, rowid, category, score from temp_table order by rowid, category asc

2. In the external query, I am effectively picking the max() values of each category, for each of the rankings as obtained in the source SQL query.