0

i need your help for a pgsql request. i have in my db 2 tables: squads(name) and account(account_name, score, squad_name). what i would like to do is to list all squad and for each squad, to get the score of each account. So i have same number of column than accounts. How can i do that ? knowing that accounts and squads can change often so it must be dynamic.

here is a result i would like to have: query result

EDIT

as asked, here is a simple model of my db(i just have more evaluation types like email etc...). my model

for exemple i do lots of evaluations of calls that employees take. an evaluation belongs to a squad and to an account. i join tables by the names of squad and account. now i need to see the score of the evaluation(per squad and per account as described in the first picture). idealy columns are the accounts

Thank you

gauvain
  • 3
  • 2
  • What do you mean by dynamic? Have you tried equijoin to start with? – HumayunM Jun 26 '20 at 09:41
  • i mean dynamic because i don't want to write in the request all names of accounts. columns should be the result of :select * from accounts. i edited my question with a picture of what i would like to do. i tried different joins but without success. – gauvain Jun 26 '20 at 10:06
  • Please also share the table definitions and also if any relationships between them and sample data in each of these tables. – HumayunM Jun 26 '20 at 11:06
  • question edited. thanks – gauvain Jun 26 '20 at 13:23
  • Seems like you have to make a sort of dynamic sql which will transpose your accounts into columns. `crosstab` should [help](https://stackoverflow.com/a/23061730/599164) – Frankie Drake Jun 26 '20 at 14:40
  • two questions, (1) why doesn't your accounts table have account_name, if your squads table have squad_name? if call_evaluation has all the attributes, what purpose does the many to 1 relationship serve i.e. why would I join? – HumayunM Jun 29 '20 at 09:07

0 Answers0