-1

I have table like this:

+-------+------------+---------+-------------------------+
| Id    | Mission    | Status  | Time                    |
+-------+------------+---------+-------------------------+
| 1     | Mission 1  | Start   | 2020-06-01 09:50:00     |
| 2     | Mission 1  | Running | 2020-06-01 10:50:00     |
| 3     | Mission 1  | Stop    | 2020-06-01 11:50:00     |
+-------+------------+---------+-------------------------+

and my expect output is

+------------+-------------------------+---------------------+---------------------+
| Mission    | Start                   | Running             | Stop                |
+------------+-------------------------+---------------------+---------------------+
| Mission 1  | 2020-06-01 09:50:00     | 2020-06-01 10:50:00 | 2020-06-01 11:50:00 |
+------------+-------------------------+-------------------------------------------+

I found some questions related to my problem but those are not exactly work with mine.

juergen d
  • 201,996
  • 37
  • 293
  • 362
Dina
  • 146
  • 2
  • 13
  • 1
    Does this answer your question? [SQL row value as column name](https://stackoverflow.com/questions/3231312/sql-row-value-as-column-name) – Rich Jun 26 '20 at 06:52
  • 3 subqueries, each for separate Status, joined by Mission to a subquery which gets distinct missions list. PS. (Mission, Status) must be defined as UNIQUE in source table. – Akina Jun 26 '20 at 06:53
  • @Rich Look carefully on DBMS tags. – Akina Jun 26 '20 at 06:54
  • 1
    ... and again somebody upvotes "Does this answer your question?" referenced to a topic which is ABSOLUTELY non-applicable. What are you looking by? – Akina Jun 26 '20 at 07:13
  • @Rich I think that question and answer are for Sql Server which has pivot function – Dina Jun 26 '20 at 07:25
  • @Akina I have considered your solution but I try to find shorter version – Dina Jun 26 '20 at 07:27
  • Shorter version provided by **juergen d** may be less effective due to grouping (depends on table structure and statistic, must be tested). – Akina Jun 26 '20 at 07:48

1 Answers1

2
select mission, 
       max(case when status = 'Start' then time end) as `Start`,
       max(case when status = 'Running' then time end) as Running,
       max(case when status = 'Stop' then time end) as Stop
from your_table
group by mission
juergen d
  • 201,996
  • 37
  • 293
  • 362