Forum > Databases

[HowTo SQLite] Setting an order within a Group-Concat

(1/1)

Zvoni:
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:

--- Quote ---The order of the concatenated elements is arbitrary.
--- End quote ---
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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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  [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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)

Navigation

[0] Message Index