Oracle grouped row counts to columns I have a table which contains this data:

- ID - PRIMARY KEY, INTEGER
- PC_ID - INTEGER
- USER_ID - INTEGER
- PAYMENT_DATETIME - TIMESTAMP
- FIO - VARCHAR2(255)


When I do a SELECT I get this (example):

1, 1, 1, 08.06.2018 17:33:26, Test
2, 1, 1, 08.06.2018 17:34:52, Test
3, 1, 1, 08.06.2018 18:01:33, Test
4, 1, 1, 08.06.2018 18:03:17, Test2
5, 1, 1, 08.06.2018 19:26:41, Test
6, 1, 1, 09.06.2018 13:22:58, Test2


So if I group them by PC_ID, USER_ID, Distinct FIO, DATE (DD. MM. YYYY), TIME (HH24) as rows to show amount of different FIO for hours 07 to 22 for each day for each user for each pc, I get:

1, 1, 08.06.2018, 17, 1
1, 1, 08.06.2018, 18, 2
1, 1, 08.06.2018, 19, 1
1, 1, 09.06.2018, 13, 1


The last column is the Count of distinct FIO.

Now I would like to transform it to:

Columns: PC_ID, USER_ID, DATE (DD. MM. YYYY), TIME (HH24) for 07 to 22 and TOTAL FOR DATE

If the count for hour is 0 then show 0:

So the structure should look like:

PC_ID, USER_ID, DATE, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, TOTAL_FIO

I was thinking of CTEs, can this be achieved using them?
Possible with a CTE, but PIVOT is the easier implementation.

If for total_fio you still wanted distinct, this would work:

SELECT * FROM
 (SELECT pc_id, 
         user_id, 
         to_char(payment_datetime,'DD.MM.YYYY') AS "date", 
         To_char(payment_datetime, 'HH24') AS "hour",
         fio,
         count(DISTINCT fio) OVER ( PARTITION BY  to_char(payment_datetime,'DD.MM.YYYY') ) AS total_fio
  FROM   foobar 
  WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22' )
PIVOT
 (Count(DISTINCT fio)
   FOR "hour" IN ('07', '08', '09', '10', '11', '12', 
                  '13', '14', '15', '16', '17', '18', 
                  '19', '20', '21', '22'));


Or if you wanted a sum of all hour columns, I'm not sure of an elegant way to do it without getting into PL/SQL:

SELECT x.*, "07" + "08" + "09" + "10" + "11" + "12" 
             + "13" + "14" + "15" + "16" + "17" + "18" 
             + "19" + "20" + "21" + "22" AS total_fio
FROM
(SELECT pc_id, 
        user_id, 
        to_char(payment_datetime,'DD.MM.YYYY') AS date_day, 
        To_char(payment_datetime, 'HH24') AS date_hour,
        fio
 FROM   foobar 
 WHERE  To_char(payment_datetime, 'HH24') BETWEEN '07' AND '22')
PIVOT
(Count(DISTINCT fio)
  FOR date_hour IN ('07' AS "07", '08' AS "08", '09' AS "09", '10' AS "10", '11' AS "11", 
                    '12' AS "12", '13' AS "13", '14' AS "14", '15' AS "15", '16' AS "16", 
                    '17' AS "17", '18' AS "18", '19' AS "19", '20' AS "20", '21' AS "21", 
                    '22' AS "22")) x;


I'm new on here so don't have enough rep to comment on your implementation, but it's a solid go at it for sure. I don't see anything wrong where you'd get inconsistent results. As for optimization, `PIVOT' does the grouping for you. So by using the CTE with the additional group by to start out, it is almost doubling the work the query has to do. My second query above is essentially an optimized version of yours (they are very similar).