In SQL how do you save an array of numbers for each entry in a column?-CodePudding

I'm trying to do something very simple but can't figure out how.

Say I have this table called "Tasks" where each task has a chosen set of users that should carry it out. The numbers in the Users column refer to the ID column of the "Users" table.

Tasks table:

 -------------- ------------- -------------- 
| Task         | Created_On  | Users        |
 -------------- ------------- -------------- 
| Task A       | 19/11/22    | 1,3,4        |
| Task B       | 19/11/22    | 1,4,5,6      |
| Task C       | 19/11/22    | 2,3,6        |
 -------------- ------------- --------------

Users table:

 -------------- ------------- 
| ID           | User        |
 -------------- ------------- 
| 1            | George      |
| 2            | John        |
| 3            | Jim         |
| 4            | James       |
| 5            | Jill        |
| 6            | Joe         |
 -------------- -------------

How do you create the "Users" column of the Tasks table? There's no "Array" column type in Oracle.

CodePudding user response：

Create a bridging table:

CREATE TABLE task_users (
  Task    CONSTRAINT task_users__task__fk REFERENCES tasks (task),
  User_id CONSTRAINT task_users__user_id__fk REFERENCES users (id),
  CONSTRAINT task_users__task__user_id__pk PRIMARY KEY(task, user_id)
);

Then you can insert the data:

INSERT INTO task_users (task, user_id)
SELECT 'Task A', 1 FROM DUAL UNION ALL
SELECT 'Task A', 3 FROM DUAL UNION ALL
SELECT 'Task A', 4 FROM DUAL UNION ALL
SELECT 'Task B', 1 FROM DUAL UNION ALL
SELECT 'Task B', 4 FROM DUAL UNION ALL
SELECT 'Task B', 5 FROM DUAL UNION ALL
SELECT 'Task B', 6 FROM DUAL UNION ALL
SELECT 'Task C', 2 FROM DUAL UNION ALL
SELECT 'Task C', 3 FROM DUAL UNION ALL
SELECT 'Task C', 6 FROM DUAL;

and the table is constrained to only contain valid users and unique task/user pairs.

CodePudding user response：

There's no "Array" column type in Oracle

Right, but there's another built-in datatype you could use - sys.odcinumberlist. Here's how:

SQL> create table task (task varchar2(10), created_on date, users sys.odcinumberlist);

Table created.

SQL> insert into task (task, created_on, users)
  2    values ('Task A', date '2022-11-19', sys.odcinumberlist(1,3,4));

1 row created.

SQL> select * from task;

TASK       CREATED_ON USERS
---------- ---------- ----------------------------------------
Task A     19.11.2022 ODCINUMBERLIST(1, 3, 4)

SQL>

So, which users are these 1, 3 and 4?

SQL> select * from users;

        ID USERNAME
---------- ----------
         1 George        --> this
         2 John
         3 Jim           --> this
         4 James         --> this
         5 Jill
         6 Joe

6 rows selected.

SQL> select t.task, u.username
  2  from task t join users u on u.id in (select * From table(t.users));

TASK       USERNAME
---------- ----------
Task A     George
Task A     Jim
Task A     James

SQL>

CodePudding user response：

If I correctly understand what you need, then, split the comma-separated integers by using regular expression, and then concatenate them by using listagg function in grouped sense like this

WITH tu AS
(  
  SELECT *
    FROM Users u
    JOIN (SELECT t.*, REGEXP_SUBSTR(Users, '[^,] ', 1, level) AS usr, level AS lvl
            FROM Tasks t
          CONNECT BY level <= REGEXP_COUNT(Users, ',')   1
                 AND PRIOR sys_guid() IS NOT NULL
                 AND PRIOR Task = Task) t
      ON u.ID = t.usr
)
SELECT task, Created_On, LISTAGG("User",', ') WITHIN GROUP (ORDER BY lvl) AS Users
  FROM tu
 GROUP BY task, Created_On