CODEWITHSUNDEEP
sqldatabaseoracle-databaseselect
Ulquiorra
Ulquiorra

Reputation: 13

Complicated SELECT statement in Oracle DB

Can you please help me with one complicated select statement?

I have a table like this:

+----+-----------+-----------+-----------------+
| ID | User_name | Situation |    Date_time    |
+----+-----------+-----------+-----------------+
|  1 | Alex      |         1 |   14.3.18 11:30 |
|  4 | Alex      |         2 |   14.3.18 11:35 |
|  6 | Alex      |         3 |   14.3.18 12:30 |
|  7 | Johnny    |         1 |   15.3.18 10:01 |
|  9 | Johnny    |         2 |   15.3.18 10:05 |
| 12 | Johnny    |         3 |   15.3.18 10:20 |
| 14 | Alex      |         1 |   20.3.18 20:00 |
| 15 | Alex      |         2 |   20.3.18 20:25 |
| 17 | Alex      |         3 |   20.3.18 21:25 |
+----+-----------+-----------+-----------------+

And I need a select statement, which will give me the following result: User_name, Date_time_1 (Date_time of situation 1), Date_time_3 (Date_time of situation 3).

*In this case the result will have just 3 rows (2 for Alex and 1 for Johnny). Each row will contain 3 columns as described above.

And sorry for the formatting - I posted that from a mobile. I will add the result table when I will get to PC.*

That's how the output should looks like:

+----+-----------+-------------+-----------------+
| ID | User_name |Date_time_1  |   Date_time_3   |
+----+-----------+-------------+-----------------+
|  1 | Alex      |14.3.18 11:30|   14.3.18 12:30 |
|  2 | Johnny    |15.3.18 10:01|   15.3.18 10:20 |
|  3 | Alex      |20.3.18 20:00|   20.3.18 21:25 |
+----+-----------+-------------+-----------------+

Upvotes: 1

Views: 75

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175706

You could use conditional aggregation:

SELECT User_name,
      MAX(CASE WHEN Situation = 1 THEN Date_time END) AS date_time_1,
      MAX(CASE WHEN Situation = 3 THEN Date_time END) AS date_time_3
FROM tab
GROUP BY User_name;

EDIT

In this case the result will have just 3 rows (2 for Alex and 1 for Johnny)

WITH cte AS (
   SELECT t.*, SUM(CASE WHEN Situation=1 THEN 1 ELSE 0 END)
          OVER(PARTITION BY User_name ORDER BY id) AS s
   FROM tab t
)
SELECT User_name,
      MAX(CASE WHEN Situation = 1 THEN Date_time END) AS date_time_1,
      MAX(CASE WHEN Situation = 3 THEN Date_time END) AS date_time_3
FROM cte
GROUP BY s, User_name;

DBFiddle Demo

Upvotes: 2

Related Questions