Search code examples
sqloracle-databasepivotaggregate

Oracle SQL - PIVOT values without aggregate

My table looks like this:

|ID|Dates|Is_Active|
|----|----|----|
|718844 |13/07/2023 18:00 - 13/07/2023 19:00|   0|
|718842 |13/07/2023 08:00 - 13/07/2023 15:00|   0|
|718844 |13/07/2023 06:00 - 13/07/2023 18:00|   0|
|718842 |13/07/2023 06:00 - 13/07/2023 08:00|   0|
|718842 |13/07/2023 15:00 - 13/07/2023 17:00|   0|
|718844 |13/07/2023 18:00 - 13/07/2023 19:00|   1|
|718844 |13/07/2023 06:00 - 13/07/2023 18:00|   1|
|718842 |13/07/2023 08:00 - 13/07/2023 10:00|   1|
|718842 |13/07/2023 13:00 - 13/07/2023 15:00|   1|
|718842 |13/07/2023 10:00 - 13/07/2023 13:00|   1|
|718842 |13/07/2023 06:00 - 13/07/2023 08:00|   1|
|718842 |13/07/2023 15:00 - 13/07/2023 17:00|   1|

and I need to transform my data to have active/inactive dates in separate columns.

|ID|Inactive|Active|
|----|----|----|
|718844 |13/07/2023 18:00 - 13/07/2023 19:00|13/07/2023 18:00 - 13/07/2023 19:00|
|718842 |13/07/2023 08:00 - 13/07/2023 15:00|13/07/2023 06:00 - 13/07/2023 18:00|
|718844 |13/07/2023 06:00 - 13/07/2023 18:00|13/07/2023 08:00 - 13/07/2023 10:00|
|718842 |13/07/2023 06:00 - 13/07/2023 08:00|13/07/2023 13:00 - 13/07/2023 15:00|
|718842 |13/07/2023 15:00 - 13/07/2023 17:00|13/07/2023 10:00 - 13/07/2023 13:00|
|718844 |                                   |13/07/2023 06:00 - 13/07/2023 08:00|
|718844 |                                   |13/07/2023 15:00 - 13/07/2023 17:00|

I was trying to use PIVOT but it requires aggregate which I don't want. Do you have any idea how can I resolve this?

Data:

                 CREATE GLOBAL TEMPORARY TABLE my_gtt (id         NUMBER
                                                      ,dates      VARCHAR2(200)
                                                      ,is_active  NUMBER
                                                      ) ON COMMIT PRESERVE ROWS;

INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 15:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 06:00 - 13/07/2023 08:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 10:00 - 13/07/2023 13:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 15:00 - 13/07/2023 17:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 13:00 - 13/07/2023 15:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718842, '13/07/2023 08:00 - 13/07/2023 10:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 0);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 06:00 - 13/07/2023 18:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 1);
INSERT INTO my_gtt (id, dates, is_active) VALUES (718844, '13/07/2023 18:00 - 13/07/2023 19:00', 0);
Solution

  • Use the ROW_NUMBER analytic function to give each row, per id and is_active value, a unique value and then PIVOT using that unique value so that you will never have more than one value being aggregated:

    SELECT id,
       inactive,
       active
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY id, is_active ORDER BY dates) AS rn
  FROM   my_gtt t
)
PIVOT ( MAX(dates) FOR is_active IN (0 AS inactive, 1 AS active) )

    Which, for your sample data, outputs:

    ID INACTIVE ACTIVE
    718842 13/07/2023 06:00 - 13/07/2023 08:00 13/07/2023 06:00 - 13/07/2023 08:00
    718842 13/07/2023 08:00 - 13/07/2023 15:00 13/07/2023 08:00 - 13/07/2023 10:00
    718842 13/07/2023 15:00 - 13/07/2023 17:00 13/07/2023 10:00 - 13/07/2023 13:00
    718842 null 13/07/2023 13:00 - 13/07/2023 15:00
    718842 null 13/07/2023 15:00 - 13/07/2023 17:00
    718844 13/07/2023 06:00 - 13/07/2023 18:00 13/07/2023 06:00 - 13/07/2023 18:00
    718844 13/07/2023 18:00 - 13/07/2023 19:00 13/07/2023 18:00 - 13/07/2023 19:00

    fiddle