Recent

Author Topic: [HowTo SQLite] Setting an order within a Group-Concat  (Read 601 times)

Zvoni

  • Hero Member
  • *****
  • Posts: 2319
[HowTo SQLite] Setting an order within a Group-Concat
« on: November 24, 2022, 11:11:58 am »
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.
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][+][-]
  1. CREATE TABLE "tbl_employee" (
  2.     "ID"    INTEGER,
  3.     "FirstName"    TEXT NOT NULL,
  4.     "LastName"    TEXT NOT NULL,    
  5.     PRIMARY KEY("ID")
  6. );
  7. CREATE TABLE "tbl_task" (
  8.     "ID"    INTEGER,
  9.     "Description"    TEXT NOT NULL,
  10.     PRIMARY KEY("ID")
  11. );
  12. CREATE TABLE "tbl_emp_task" (
  13.     "emp_id"    INTEGER,
  14.     "task_id"    INTEGER,
  15.     FOREIGN KEY("task_id") REFERENCES "tbl_task"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
  16.     FOREIGN KEY("emp_id") REFERENCES "tbl_employee"("ID") ON UPDATE CASCADE ON DELETE CASCADE,
  17.     PRIMARY KEY("emp_id","task_id")
  18. );
  19. /*Testdata */
  20. INSERT INTO tbl_employee ("ID", "FirstName", "LastName") VALUES (1, 'Tom', 'Brown'), (2, 'Robert', 'Miller'), (3, 'John', 'Smith');
  21. INSERT INTO tbl_task ("ID", "Description") VALUES (1, 'answering phones'), (2, 'preparing meeting'), (3, 'receiving visitors'), (4, 'Maintaining security'), (5, 'Arranging couriers');
  22. 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][+][-]
  1. WITH CTE AS
  2.     (SELECT
  3.         ET.emp_ID AS EID,
  4.         T.Description AS TDESC
  5.     FROM tbl_emp_task AS ET
  6.     INNER JOIN tbl_task AS T
  7.     ON T.ID=ET.task_id
  8.     ORDER BY LOWER(T.Description))  /*For Case-Sensitive: ORDER BY T.Description --> No Lower-Function*/
  9. SELECT
  10.     E.FirstName || ' ' || E.LastName AS FullName,  /*Instead of constructing this within the SELECT we could also use a GENERATED Column*/
  11.     COALESCE(C.AssignedTasks, 'No assigned Tasks') AS AssignedTasks
  12. FROM tbl_employee AS E
  13. LEFT JOIN
  14.     (SELECT
  15.         EID,
  16.         GROUP_CONCAT(TDESC, ', ') AS AssignedTasks
  17.     FROM CTE GROUP BY EID) AS C
  18. ON C.EID=E.ID
  19. GROUP BY E.FirstName || ' ' || E.LastName ORDER BY E.LastName, E.FirstName
The 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)
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

 

TinyPortal © 2005-2018