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

with cte as (
  select category, score, row_number() over (
    partition by category order by score
  ) as r
  from temp_table
)
  select
    sum(score) filter (where category = 'cat1') as cat1,
    sum(score) filter (where category = 'cat2') as cat2,
    sum(score) filter (where category = 'cat3') as cat3
  from cte
  group by r
  order by r
;

If the number of columns is known and it is reasonably small, FILTER might be a better option than CROSSTAB, which requires an extension.

Answer 2

You can use this API call - https://developers.docusign.com/docs/esign-rest-api/reference/envelopes/envelopecustomfields/create/

So, you may need 3 API calls:

1. Create envelope in "draft" status.
2. Add custom fields.
3. Send it.

There may be a way to do this with fewer API calls, but I don't know how.