sql - How to make a query using repeated WHERE clauses and LEFT JOINS more dynamic (without crostab) -

sql - How to make a query using repeated WHERE clauses and LEFT JOINS more dynamic (without crostab) -

edit: on older version of postgresql not back upwards crostab.

i have query trying improve making easier add together new value table meant roll info single user

i have write new left bring together , clause each time add together value column below called algorithms:

┌───────────────────────────────┐ │ algo │ ├───────────────────────────────┤ │ algorithm1 │ │ algorithm2 │ │ algorithm3 │ └───────────────────────────────┘

here query wrote genearte output:

select a.userid, a.algo, a.algo1_cnt, b.algo, b.algo2t_cnt, c.algo, c.algo3_cnt (select userid, algo, count(*) algo1_cnt test_table (algo = 'algorithm1') grouping 1,2 ) left outer bring together ( select userid, algo, count(*) algo2_cnt test_table (algo = 'algorithm2') grouping 1,2 ) b on (a.userid = b.userid) left outer bring together ( select userid, algo, count(*) algo3_cnt test_table (algo = 'algorithm3') grouping 1,2 ) c on (a.userid = c.userid)

the output of query looks like:

┌──────────────────────┬────────────────┬───────────┬───────┬───────────┬───────────────────────────────┬───────────┐ │ userid │ algo1 │ algo1_cnt │ algo2 │ algo2_cnt │ algo3 │ algo3_cnt │ ├──────────────────────┼────────────────┼───────────┼───────┼───────────┼───────────────────────────────┼───────────┤ │ user1 │ algo1 │ 3 │ │ │ algo3 │ 2 │ │ user2 │ algo1 │ 2 │ │ │ │ │

question: best way modify query able read distinct values algo column in dynamic fashion , generate same outpuy?

what mean if add together new value called algorithm4 algo column can levarage pl/pgsql or other dyanmic recurision generate same output without having utilize (algo = 'algorithm4')?

you can utilize crosstab if can split arrays somewhere else much simpler

select user_id, array_agg(algo) algo, array_agg(algo_count) algo_count ( select userid, algo, count(*) algo_count test_table grouping 1, 2 ) s grouping 1

json aficionados can have it

select user_id, format( '{%s}', string_agg(format('"%s": %s', algo, algo_count), ', ') )::json algo_obj ( select userid, algo, count(*) algo_count test_table grouping 1, 2 ) s grouping 1

sql postgresql