MySQL: Combine multiple rows (with null values) into one row?

Question

I have a table called code_status:

  Code     Message
    1     "Start"
    2     "In process"
    3     "Finish"

And another table called history

 ID   Code    Name      time_date
 1     2      Jon       1/2/15
 31    1      Abby      2/1/15
 12    3      Sara      3/3/15
 31    2      Abby      2/3/15
 31    3      Abby      2/5/15
 8     2      Max       1/22/15

I want to create an history_view with the following schema:

history_view (id, name, start_date, process_date, finish_date)

If the date is not given, it would just be NULL

So it would look like

ID   Name    start_date    process_date      finish_date
31   Abby     2/1/15          2/3/15           2/5/15
1    Jon      NULL            1/2/15            NULL
... etc

So i started off by doing :

CREATE VIEW history_view
AS SELECT h.id, h.name, 
(CASE WHEN h.code = 1 THEN time_date) AS start_date,
(CASE when h.code = 2 THEN time_date) AS process_date,
(CASE when h.code = 3 THEN time_date) AS finish_date
FROM history h;

I would get a result like the following below though:

ID   Name    start_date    process_date      finish_date
31   Abby     2/1/15         NULL               NULL
31   Abby      NULL          2/3/15             NULL
31   Abby      NULL          NULL             2/5/15
... ETC

Is there any way to consolidate the rows together ?

Brian DeMilia · Accepted Answer · 2015-03-09 03:42:18Z

3

When you say consolidate you're referring to aggregation, you can use an aggregate function, such as min:

CREATE VIEW history_view
AS SELECT h.id, h.name, 
min(CASE WHEN h.code = 1 THEN time_date) AS start_date,
min(CASE when h.code = 2 THEN time_date) AS process_date,
min(CASE when h.code = 3 THEN time_date) AS finish_date
FROM history h
group by h.id, h.name;

answered Mar 9, 2015 at 3:42

Brian DeMilia

13.3k1 gold badge25 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Kara Over a year ago

what does a min(case) do exactly?

Brian DeMilia Over a year ago

Combined with the group by clause it will show the first time_date value where the code is 1, for each id and name combination (in the case of your first case statement). In the second case statement, it will show the first time_date value where the code is 2, for each id and name combination. Because there is only one such row for each combination of [id, name, code] it doesn't matter whether you use min, max, etc. so long as you use an aggregate function.

Collectives™ on Stack Overflow

MySQL: Combine multiple rows (with null values) into one row?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related