How to split these multiple rows in SQL?

Question

I am currently studying SQL and I am still a newbie. I have this task where I need to split some rows with various entries like dates and user IDs. I really need help

+-------+------------------------------+---------------------------+
| TYPE  |            DATES             |         USER _ID          |
+-------+------------------------------+---------------------------+
| WORK  | ["2022-06-02", "2022-06-03"] | {74042,88357,83902,88348} |
| LEAVE | ["2022-05-16", "2022-05-26"] | {83902,74042,88357,88348} |
+-------+------------------------------+---------------------------+

the end result should look like this. the user id's should be aligned or should be in the same as their respective dates.

+-------+------------+---------+
| TYPE  |   DATES    | USER_ID |
+-------+------------+---------+
| LEAVE | 05/16/2022 |   74042 |
| LEAVE | 05/16/2022 |   88357 |
| LEAVE | 05/16/2022 |   88348 |
| LEAVE | 05/16/2022 |   83902 |
| LEAVE | 05/26/2022 |   74042 |
| LEAVE | 05/26/2022 |   88357 |
| LEAVE | 05/26/2022 |   88348 |
| LEAVE | 05/26/2022 |   83902 |
| WORK  | 06/2/2022  |   74042 |
| WORK  | 06/2/2022  |   88357 |
| WORK  | 06/2/2022  |   88348 |
| WORK  | 06/2/2022  |   83902 |
| WORK  | 06/3/2022  |   74042 |
| WORK  | 06/3/2022  |   88357 |
| WORK  | 06/3/2022  |   88348 |
| WORK  | 06/3/2022  |   83902 |
+-------+------------+---------+

Please provide enough code so others can better understand or reproduce the problem. — Community, Jan 22 '23 at 16:29
Your `LEAVE` output does not match your written criteria , the user id's are not in the order specified. — Adrian Klaver, Jan 22 '23 at 17:11

Adrian Klaver · Answer 1 · 2023-01-22T18:07:32.860

Create table:

CREATE TABLE work_leave (
    TYPE varchar,
    DATES date,
    USER_ID integer
);

INSERT INTO work_leave
    VALUES ('LEAVE', '05/16/2022', 74042),
    ('LEAVE', '05/16/2022', 88357),
    ('LEAVE', '05/16/2022', 88348),
    ('LEAVE', '05/16/2022', 83902),
    ('LEAVE', '05/26/2022', 74042),
    ('LEAVE', '05/26/2022', 88357),
    ('LEAVE', '05/26/2022', 88348),
    ('LEAVE', '05/26/2022', 83902),
    ('WORK', '06/2/2022', 74042),
    ('WORK', '06/2/2022', 88357),
    ('WORK', '06/2/2022', 88348),
    ('WORK', '06/2/2022', 83902),
    ('WORK', '06/3/2022', 74042),
    ('WORK', '06/3/2022', 88357),
    ('WORK', '06/3/2022', 88348),
    ('WORK', '06/3/2022', 83902);

WITH date_ends AS (
    SELECT
        type,
        ARRAY[min(dates),
        max(dates)] AS dates
    FROM
        work_leave
    GROUP BY
        type
),
users AS (
    SELECT
        type,
        array_agg(DISTINCT (user_id)
        ORDER BY user_id) AS user_ids
FROM
    work_leave
GROUP BY
    type
)
SELECT
    de.type,
    de.dates,
    u.user_ids
FROM
    date_ends AS de 
JOIN 
    users as u 
ON de.type = u.type;

type  |          dates          |         user_ids          
-------+-------------------------+---------------------------
 LEAVE | {05/16/2022,05/26/2022} | {74042,83902,88348,88357}
 WORK  | {06/02/2022,06/03/2022} | {74042,83902,88348,88357}

score 1 · Answer 2 · answered Jan 23 '23 at 01:04

I adjusted the data slightly for simplicity. Here's one idea:

WITH rows (type, dates, user_id) AS (
       VALUES ('WORK',  array['2022-06-02', '2022-06-03'], array[74042,88357,83902,88348])
            , ('LEAVE', array['2022-05-16', '2022-05-26'], array[83902,74042,88357,88348])
     )
SELECT r1.type, x.*
  FROM rows AS r1
  CROSS JOIN LATERAL (
      SELECT r2.dates, r3.user_id
        FROM unnest(r1.dates)   AS r2(dates)
           , unnest(r1.user_id) AS r3(user_id)
    ) AS x
;

The fiddle

The result:

type	dates	user_id
WORK	2022-06-02	74042
WORK	2022-06-02	88357
WORK	2022-06-02	83902
WORK	2022-06-02	88348
WORK	2022-06-03	74042
WORK	2022-06-03	88357
WORK	2022-06-03	83902
WORK	2022-06-03	88348
LEAVE	2022-05-16	83902
LEAVE	2022-05-16	74042
LEAVE	2022-05-16	88357
LEAVE	2022-05-16	88348
LEAVE	2022-05-26	83902
LEAVE	2022-05-26	74042
LEAVE	2022-05-26	88357
LEAVE	2022-05-26	88348

How to split these multiple rows in SQL?

2 Answers2