Search code examples
sqloracle-databasepivotcaseoracle12c

Group all rows after 4 rows and create 5th row of grouped rows

I have a Query:

SELECT *
FROM   (
  SELECT WORKING_DAYS, ACTIVITY
  FROM TABLE
)
PIVOT (
  COUNT(ACTIVITY)
  FOR ACTIVITY IN (
     6 AS NL6,
     7 AS NL7,
     8 AS NL8,
     9 AS NL9,
    11 AS NL11
  )
)
ORDER BY WORKING_DAYS;

Output of the above query is as below:

enter image description here

I was trying to have output as below but I am having no clue how to proceed:

enter image description here

Can anyone help here and let me know best possible solution for this?

I am using Oracle version - 12.1.0.2

Solution

  • You can convert WORKING DAYS to your desired format in the sub-query before you PIVOT:

    SELECT *
FROM   (
  SELECT CASE
         WHEN WORKING_DAYS <= 4
         THEN TO_CHAR( WORKING_DAYS )
         ELSE '>4'
         END AS working_days,
         ACTIVITY
  FROM   table_name
)
PIVOT (
  COUNT(ACTIVITY)
  FOR ACTIVITY IN (
     6 AS NL6,
     7 AS NL7,
     8 AS NL8,
     9 AS NL9,
    11 AS NL11
  )
)
ORDER BY WORKING_DAYS;

    Which, for your sample data:

    CREATE TABLE table_name ( working_days, activity ) AS
SELECT  1, 6 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT  3, 6 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT  4, 9 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT  7, 6 FROM DUAL CONNECT BY LEVEL <= 7 UNION ALL
SELECT  8, 6 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT  8, 8 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT  9, 6 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 11, 8 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 12, 6 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 13, 6 FROM DUAL CONNECT BY LEVEL <= 3 UNION ALL
SELECT 13, 8 FROM DUAL CONNECT BY LEVEL <= 2 UNION ALL
SELECT 14, 9 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 16, 8 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 18, 8 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 24, 9 FROM DUAL CONNECT BY LEVEL <= 1 UNION ALL
SELECT 25, 7 FROM DUAL CONNECT BY LEVEL <= 1;

    Outputs:

    WORKING_DAYS | NL6 | NL7 | NL8 | NL9 | NL11
:----------- | --: | --: | --: | --: | ---:
1            |   2 |   0 |   0 |   0 |    0
3            |   1 |   0 |   0 |   0 |    0
4            |   0 |   0 |   0 |   2 |    0
>4           |  16 |   1 |   6 |   2 |    0

    db<>fiddle here