Edit output from SQL query to agrupate values

Question

First of all, I appreciate your help with my problem. I am currently working in a data base using sql in python. This is my code:

from tabulate import tabulate #the output generates a table
sql_query = "SELECT DISTINCT dc.name AS disease_code_name, d.drug_name FROM disease_code dc, drug d, drug_disease dd WHERE dc.code_id=dd.code_id AND dd.drug_id=d.drug_id AND dd.inferred_score=(SELECT MAX(dd.inferred_score));"
cursor.execute(sql_query)
score_table = [['1','2']]
for row in cursor:
    nom = row[0]
    sco = row[1]
    list_d = [nom,sco]
    score_table.append(list_d)
print(tabulate(score_table, headers='firstrow', tablefmt='fancy_grid'))

The output that it generates is something similar to this:

1       2
apple   fruit
orange  fruit
lemon   fruit
pig     animal
lion    animal
tiger   animal

But I want something like this:

1      2
fruit  apple, lemon, orange
animal pig, lion, tiger

Can you help me please? Thank you very much for your help

Are you able to share `specific query` with us? Using MySQL `GROUP BY` and `GROUP_CONCAT` inside the query itself may be the fastest and easiest way to do this. — Wander Nauta, Nov 30 '21 at 12:57

score 0 · Answer 1 · answered Nov 30 '21 at 13:02

0

Just using GROUP BY you can solve the problem. Thank you to Wander Nauta for his help

answered Nov 30 '21 at 13:02

quik1399

175
8

Edit output from SQL query to agrupate values

1 Answers1