[HowTo SQLite] Setting an order within a Group-Concat
lazarus / 16-Databases

----------------------------------------------------------------------
Zvoni · 2022-11-24 10:11

In another Forum i helped out an User who was struggling with the
following:
He had a table "tbl_employee" and a table "tbl_task", which were
connected in an "m:m"-scenario via "tbl_emp_task"

He wanted to query his SQLite-Database to show for all employees their
assigned tasks, but the tasks as a single text, preferably in
alphabetic order
Example
employee    task
John Smith    task1, task4, task6
Robert Miller    task3, task4, task5

The GROUP_CONCAT-Function of SQLite doesn't have a ORDER BY-Modifier
(Compared to other DBMS)
The documenation on sqlite.org even says:
QuoteThe order of the concatenated elements is arbitrary.Though i
think, the order is the order the elements are "presented", in case of
a base-table my guess is the (hidden) row_id

Therefore i developed a solution as follows:
Setup for test-Database
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE "tbl_employee" (    "ID"    INTEGER,    "FirstName"    TEXT NOT
NULL,    "LastName"    TEXT NOT NULL,        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"));/*Testdata */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);Note the combined
Primary Key for tbl_emp_task consisting of the Foreign Keys.
That way we can make sure that each employee can be assigned a
specific task only once


and here the solution:
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i WITH CTE
AS    (SELECT        ET.emp_ID AS EID,        T.Description AS TDESC 
 FROM tbl_emp_task AS ET    INNER JOIN tbl_task AS T    ON
T.ID=ET.task_id    ORDER BY LOWER(T.Description))  /*For Case-
Sensitive: ORDER BY T.Description --> No Lower-Function*/SELECT   
E.FirstName || ' ' || E.LastName AS FullName,  /*Instead of
constructing this within the SELECT we could also use a GENERATED
Column*/    COALESCE(C.AssignedTasks, 'No assigned Tasks') AS
AssignedTasksFROM tbl_employee AS ELEFT JOIN    (SELECT        EID,  
    GROUP_CONCAT(TDESC, ', ') AS AssignedTasks    FROM CTE GROUP BY
EID) AS CON C.EID=E.IDGROUP BY E.FirstName || ' ' || E.LastName ORDER
BY E.LastName, E.FirstNameThe order within the Group-Concat is Case-
Insensitive!!
If you want Case-Sensitive order, remove the "Lower"-Function within
the CTE.

btw: Using this you could use the returned Column "AssignedTasks" in
DB-bound controls (DB-Grid)