[SQLite-Tutorial] Wie bekomme ich eine Reihenfolge im GROUP_CONCAT hin

Zvoni

Erfahrenes Mitglied
Mahlzeit.
In einem anderen Forum habe ich einem User ausgeholfen ein Problem mit SQLite zu lösen.
Der User hatte eine Tabelle "employee" und eine Tabelle "Tasks" welche per m:n-Beziehung über eine Tabelle "emp_tasks" verknüpft sind.
Der User wollte zu allen "employees" die Ihnen zugewiesenen Aufgaben abrufen, die Aufgaben jedoch in einem zusammenhängenden String
Beispiel:
employeeTask
John Smithtask1, task4, task6
Robert Millertask2, task4, task6, task8

Die GROUP_CONCAT-Funktion von SQLite hat keinen ORDER BY-Parameter (wie aus anderen DBMS bekannt).
Die Dokumentation auf sqlite.org besagt sogar:
The order of the concatenated elements is arbitrary.
Bedeutet: Wörtlich übersetzt "willkürlich", wobei ich eher darauf tippe, die Reihenfolge ist dieselbe wie auch in der Tabelle selbst (also gemäss interner ROW_ID). Kann mich aber auch irren.

Ich habe daraufhin zwei Workarounds gefunden bzw. erarbeitet eine Reihenfolge in das GROUP_CONCAT zu bekommen
Anbei das Setup für eine Test-Datenbank in SQLite
SQL:
CREATE TABLE "tbl_employee" (
    "ID"    INTEGER,
    "FirstName"    TEXT NOT NULL,
    "LastName"    TEXT NOT NULL,
    "FullName" TEXT GENERATED ALWAYS AS ("FirstName" || ' ' || "LastName") VIRTUAL,
    PRIMARY KEY("ID")
);
CREATE TABLE "tbl_task" (
    "ID"    INTEGER,
    "Description"    TEXT NOT NULL,
    PRIMARY KEY("ID")
);
CREATE TABLE "tbl_emp_task" (
    "emp_id"    INTEGER,
    "task_id"    INTEGER,
    FOREIGN KEY("task_id") REFERENCES "tbl_task"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    FOREIGN KEY("emp_id") REFERENCES "tbl_employee"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
    PRIMARY KEY("emp_id","task_id")
);
INSERT INTO tbl_employee ("ID", "FirstName", "LastName") VALUES (1, 'Tom', 'Brown'), (2, 'Robert', 'Miller'), (3, 'John', 'Smith');
INSERT INTO tbl_task ("ID", "Description") VALUES (1, 'answering phones'), (2, 'preparing meeting'), (3, 'receiving visitors'), (4, 'Maintaining security'), (5, 'Arranging couriers');
INSERT INTO tbl_emp_task ("emp_id", "task_id") VALUES (1,1), (1,2), (1,4), (2,2),(2,3),(2,5);
Und hier die zwei Varianten:
SQL:
SELECT
    E.FullName,
    COALESCE(T.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
FROM tbl_employee AS E
LEFT JOIN
    (SELECT DISTINCT
        ET.emp_ID,
        GROUP_CONCAT(TA.Description) OVER(PARTITION BY ET.emp_ID ORDER BY Lower(TA.Description) ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS AssignedTasks
    FROM tbl_emp_task AS ET
    INNER JOIN tbl_task AS TA
    ON TA.ID=ET.task_id) AS T
ON T.emp_id=E.ID
GROUP BY E.FullName ORDER BY E.LastName, E.FirstName
SQL:
With CTE(EID,TID,TDESC) AS
    (SELECT
        ET.emp_ID,
        ET.task_id,
        T.Description
    FROM tbl_emp_task AS ET
    INNER JOIN tbl_task AS T
    ON T.ID=ET.task_id
    ORDER BY Lower(T.Description))
SELECT
    E.FullName,
    COALESCE(C.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
FROM tbl_employee AS E
LEFT JOIN
    (SELECT
        EID,
        GROUP_CONCAT(TDESC, ', ') AS AssignedTasks
    FROM (SELECT EID,TID,TDESC FROM CTE) GROUP BY EID) AS C
ON C.EID=E.ID
GROUP BY E.FullName ORDER BY E.LastName, E.FirstName
Die Reihenfolge ignoriert Gross/Kleinschreibung in "Description". Will man das nicht haben, einfach die Lower-Funktion entfernen

btw: Im Zuge meiner Recherche bin ich auf die GENERATED COLUMNS von SQLite gestossen.
Feines Feature.
Spart man sich den "Klassiker" das Zeug im SELECT-Statement zusammenzubauen
SQL:
SELECT FirstName, LastName, (Firstname || ' ' || LastName) As FullName FROM tbl_employee

Viel Spass

EDIT: Vergessen:
Gilt ab SQLite Version 3.31.0 (2020-01-22)
 
Zuletzt bearbeitet: