How to GROUP_CONCAT between given rows? I have a table as

id  diary
1   breakfast
2   walk
3   start
4   office works
5   office projects
6   end
7   taxi
8   start
9   preparing for meeting
10  doing the meeting
11  end
12  night


I want to CONCAT rows between start and end to product table:

id  type      diary
1             breakfast
2             walk
3   concated  office works, office projects
7             taxi
8   concated  preparing for meeting, doing the meeting
12            night


I think I need to use GROUP_CONCAT within a IF STATEMENT, but have no idea how to implement it.
Here is the query you need:
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;

First, here is your sample data
mysql&gt; use all_db
Database changed
mysql&gt; drop table if exists diary;
Query OK, 0 rows affected (0.03 sec)

mysql&gt; create table diary
    -&gt; (
    -&gt;     id int not null auto_increment,
    -&gt;     diary varchar(25),
    -&gt;     primary key (id)
    -&gt; );
Query OK, 0 rows affected (0.11 sec)

mysql&gt; insert into diary (diary) values ('breakfast'),('walk'),('start'),
    -&gt; ('office works'),('office projects'),('end'),('taxi'),('start'),
    -&gt; ('preparing for meeting'),('doing the meeting'),('end'),('night');
Query OK, 12 rows affected (0.06 sec)
Records: 12  Duplicates: 0  Warnings: 0

mysql&gt; select * from diary;
+----+-----------------------+
| id | diary                 |
+----+-----------------------+
|  1 | breakfast             |
|  2 | walk                  |
|  3 | start                 |
|  4 | office works          |
|  5 | office projects       |
|  6 | end                   |
|  7 | taxi                  |
|  8 | start                 |
|  9 | preparing for meeting |
| 10 | doing the meeting     |
| 11 | end                   |
| 12 | night                 |
+----+-----------------------+
12 rows in set (0.00 sec)

mysql&gt;

Using iterative variable manipulation
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select * from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A;

Here is what it generates:
mysql&gt; set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @inc = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; select * from
    -&gt; (select
    -&gt;     @inc      := IF(@groupon=0,1,0)           inc,
    -&gt;     @groupnum := @groupnum + @inc             grpn,
    -&gt;     @s_tag    := IF(diary='start',1,0)        stag,
    -&gt;     @e_tag    := IF(diary='end',  1,0)        etag,
    -&gt;     @groupon  := IF(diary='start',1,@groupon) g1,
    -&gt;     @groupon  := IF(diary='end',  0,@groupon) g2,
    -&gt;     (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    -&gt;     id,diary from diary
    -&gt; ) A;
+-----+------+------+------+------+------+---------+----+-----------------------+
| inc | grpn | stag | etag | g1   | g2   | ord     | id | diary                 |
+-----+------+------+------+------+------+---------+----+-----------------------+
|   1 |    1 |    0 |    0 |    0 |    0 |       0 |  1 | breakfast             |
|   1 |    2 |    0 |    0 |    0 |    0 |       0 |  2 | walk                  |
|   1 |    3 |    1 |    0 |    1 |    1 |       1 |  3 | start                 |
|   0 |    3 |    0 |    0 |    1 |    1 |       0 |  4 | office works          |
|   0 |    3 |    0 |    0 |    1 |    1 |       0 |  5 | office projects       |
|   0 |    3 |    0 |    1 |    1 |    0 | 1000000 |  6 | end                   |
|   1 |    4 |    0 |    0 |    0 |    0 |       0 |  7 | taxi                  |
|   1 |    5 |    1 |    0 |    1 |    1 |       1 |  8 | start                 |
|   0 |    5 |    0 |    0 |    1 |    1 |       0 |  9 | preparing for meeting |
|   0 |    5 |    0 |    0 |    1 |    1 |       0 | 10 | doing the meeting     |
|   0 |    5 |    0 |    1 |    1 |    0 | 1000000 | 11 | end                   |
|   1 |    6 |    0 |    0 |    0 |    0 |       0 | 12 | night                 |
+-----+------+------+------+------+------+---------+----+-----------------------+
12 rows in set (0.00 sec)

mysql&gt;

With a few refinements

ignoring ord=1 and ord=1000000
GROUP BY on grpn

Here is the final query first mentioned
set @groupnum = 0;
set @groupon = 0;
set @inc = 1;
select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
(select
    @inc      := IF(@groupon=0,1,0)           inc,
    @groupnum := @groupnum + @inc             grpn,
    @s_tag    := IF(diary='start',1,0)        stag,
    @e_tag    := IF(diary='end',  1,0)        etag,
    @groupon  := IF(diary='start',1,@groupon) g1,
    @groupon  := IF(diary='end',  0,@groupon) g2,
    (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    id,diary from diary
) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;

Here it is executed:
mysql&gt; set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @groupon = 0;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; set @inc = 1;
Query OK, 0 rows affected (0.00 sec)

mysql&gt; select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from
    -&gt; (select
    -&gt;     @inc      := IF(@groupon=0,1,0)           inc,
    -&gt;     @groupnum := @groupnum + @inc             grpn,
    -&gt;     @s_tag    := IF(diary='start',1,0)        stag,
    -&gt;     @e_tag    := IF(diary='end',  1,0)        etag,
    -&gt;     @groupon  := IF(diary='start',1,@groupon) g1,
    -&gt;     @groupon  := IF(diary='end',  0,@groupon) g2,
    -&gt;     (@ordernum:=@e_tag*1000000+@s_tag)      ord,
    -&gt;     id,diary from diary
    -&gt; ) A WHERE ord NOT IN (1,1000000) GROUP BY grpn;
+------+-----------------------------------------+
| grpn | itinerary_step                          |
+------+-----------------------------------------+
|    1 | breakfast                               |
|    2 | walk                                    |
|    3 | office works,office projects            |
|    4 | taxi                                    |
|    5 | preparing for meeting,doing the meeting |
|    6 | night                                   |
+------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql&gt;

Give it a Try !!!
CAVEAT
I tried out @a1ex07's query against my sample data
mysql&gt; select d.id1,
    -&gt; CASE
    -&gt; WHEN COUNT(*) &gt;1 THEN 'concatenated'
    -&gt; ELSE NULL
    -&gt; END AS `type`,
    -&gt; GROUP_CONCAT(
    -&gt; case
    -&gt; when diary = 'start' or diary = 'end' then null
    -&gt; else diary
    -&gt; end
    -&gt; order by id
    -&gt; ) as diary
    -&gt; FROM
    -&gt; (
    -&gt; SELECT x.id, x.diary,
    -&gt; CASE WHEN start_id IS NULL OR end_id IS NULL OR start_id &gt; end_id THEN id
    -&gt; ELSE start_id
    -&gt; END as id1,
    -&gt; end_id
    -&gt; FROM
    -&gt; (
    -&gt;     select a.*,
    -&gt;     (select max(id) from diary b where b.diary ='start' and b.id &lt;=a.id) as start_id,
    -&gt;     (select min(id) from diary b where b.diary ='end' and b.id &gt;=a.id) as end_id
    -&gt;
    -&gt;      from diary a
    -&gt;  )x
    -&gt; )d
    -&gt; group by id1,end_id;
+------+--------------+-----------------------------------------+
| id1  | type         | diary                                   |
+------+--------------+-----------------------------------------+
|    1 | NULL         | breakfast                               |
|    2 | NULL         | walk                                    |
|    3 | concatenated | office works,office projects            |
|    3 | NULL         | taxi                                    |
|    8 | concatenated | preparing for meeting,doing the meeting |
|   12 | NULL         | night                                   |
+------+--------------+-----------------------------------------+
6 rows in set (0.00 sec)

mysql&gt;

By George, it works !!! He get's +1 for getting his answer in first.