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?
Best Answer
Possible with a CTE, but PIVOT is the easier implementation.
If for total_fio you still wanted distinct, this would work:
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: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).