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

CodePudding user response：

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}

CodePudding user response：

WITH dates_users AS (
  SELECT
    type,
    DATE_FORMAT(UNNEST(dates), '%m/%d/%Y') as date,
    UNNEST(user_id) as user_id
  FROM table_name
)

SELECT type, date, user_id FROM dates_users
UNION ALL
SELECT type, date, user_id FROM dates_users

CodePudding user response：

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: