skip to Main Content

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)


Please signup or login to give your own answer.
Back To Top
Search