In oracle, I can use wm_concat to implement row-to-column conversion, as follows:
SELECT * FROM STU_TEST
name | project |
---|---|
Alice | Machine Learning |
Alice | Data Science |
Bob | Web Development |
Bob | Mobile Development |
Carol | Data Science |
Carol | Cloud Computing |
David | Artificial Intelligence |
SELECT NAME,wm_concat(PROJECT) FROM STU_TEST GROUP BY NAME
name | wm_concat(PROJECT) |
---|---|
Alice | Machine Learning,Data Science |
Bob | Web Development,Mobile Development |
Carol | Data Science,Cloud Computing |
David | Artificial Intelligence |
I want to implement this query using django orm, I tried the following, without success.
它返回了错误:cx_Oracle.DatabaseError: ORA-00937: not a single-group group function
from django.db.models import CharField, Value
from django.db.models.functions import Concat
from django.db.models.aggregates import Count
from myapp.models import StuTest
from django.db.models import Func
class GroupConcat(Func):
function = 'LISTAGG'
template = '%(function)s(%(expressions)s, ',') WITHIN GROUP (ORDER BY %(expressions)s)'
results = StuTest.objects.values('name').annotate(project_concat=GroupConcat('project')).order_by('name')
for result in results:
print(result['name'], result['project_concat'])
How to implement this query? (In the actual environment, group by may not only include the name field, but also other fields)