CODEWITHSUNDEEP
sqloracle-database
User104163
User104163

Reputation: 579

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

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.

Upvotes: 0

Views: 86

Answers (3)

d r
d r

Reputation: 7801

As the answer is already accepted here is an option that could be used to insert data into bridging table from MTO's answer. SQL MODEL clause is used here:

SELECT  t.TASK, t.CREATED_ON, t.USERS, t.IDS_COUNT, t.IDX "ID_NO", t.ID, u.USER_NAME
FROM  ( Select *
        From   (    Select  TASK, CAST(0 As VarChar2(32)) "ID", USERS, CREATED_ON,
                            Coalesce(Length(USERS) - Length(Replace(USERS,',',null)), Length(USERS), 0) + 1 "IDS_COUNT"
                    From    tasks
               )
        MODEL
            PARTITION BY    (TASK)
            DIMENSION BY    (0 "IDX")
            MEASURES    (USERS, CREATED_ON, IDS_COUNT, ID)
            RULES   ITERATE(6)  -- declare bigger number than expected number of elements in list
                (
                    USERS[ITERATION_NUMBER] = USERS[0], CREATED_ON[ANY] = CREATED_ON[0], IDS_COUNT[ANY] = IDS_COUNT[0],
                    ID[ITERATION_NUMBER] = CASE 
                                              WHEN ITERATION_NUMBER BETWEEN 1 And IDS_COUNT[0] 
                                              THEN  SubStr(SubStr(',' || USERS[0] || ',', InStr(',' || USERS[0] || ',', ',', 1, ITERATION_NUMBER)+1), 1, InStr(',' || USERS[0] || ',', ',', 1, ITERATION_NUMBER+1) - InStr(',' || USERS[0] || ',', ',', 1, ITERATION_NUMBER) - 1) 
                                           END   
                )
    ) t
INNER JOIN users u ON(u.ID = t.ID)  --LEFT JOIN if you want to see possible unmatched IDs - USER_NAME would be Null
Where IDX Between 1 And IDS_COUNT
Order By  TASK, IDX

Result:

TASK CREATED_ON USERS IDS_COUNT ID_NO ID USER_NAME
Task A 19-NOV-22 1,3,4 3 1 1 George
Task A 19-NOV-22 1,3,4 3 2 3 Jim
Task A 19-NOV-22 1,3,4 3 3 4 James
Task B 19-NOV-22 1,4,5,6 4 1 1 George
Task B 19-NOV-22 1,4,5,6 4 2 4 James
Task B 19-NOV-22 1,4,5,6 4 3 5 Jill
Task B 19-NOV-22 1,4,5,6 4 4 6 Joe
Task C 19-NOV-22 2,3,6 3 1 2 John
Task C 19-NOV-22 2,3,6 3 2 3 Jim
Task C 19-NOV-22 2,3,6 3 3 6 Joe

Regards...

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142798

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>

Upvotes: 1

MT0
MT0

Reputation: 168137

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.

Upvotes: 4

Related Questions