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?