Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
add comment

2 Answers 2

up vote 4 down vote accepted

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> use all_db
Database changed
mysql> drop table if exists diary;
Query OK, 0 rows affected (0.03 sec)

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

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

mysql> 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>

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> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> 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;
+-----+------+------+------+------+------+---------+----+-----------------------+
| 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>

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> set @groupnum = 0;
Query OK, 0 rows affected (0.00 sec)

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

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

mysql> 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;
+------+-----------------------------------------+
| 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>

Give it a Try !!!

CAVEAT

I tried out @a1ex07's query against my sample data

mysql> select d.id1,
    -> CASE
    -> WHEN COUNT(*) >1 THEN 'concatenated'
    -> ELSE NULL
    -> END AS `type`,
    -> GROUP_CONCAT(
    -> case
    -> when diary = 'start' or diary = 'end' then null
    -> else diary
    -> end
    -> order by id
    -> ) as diary
    -> FROM
    -> (
    -> SELECT x.id, x.diary,
    -> CASE WHEN start_id IS NULL OR end_id IS NULL OR start_id > end_id THEN id
    -> ELSE start_id
    -> END as id1,
    -> end_id
    -> FROM
    -> (
    ->     select a.*,
    ->     (select max(id) from diary b where b.diary ='start' and b.id <=a.id) as start_id,
    ->     (select min(id) from diary b where b.diary ='end' and b.id >=a.id) as end_id
    ->
    ->      from diary a
    ->  )x
    -> )d
    -> 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>

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

share|improve this answer
    
After I saw results of my query in your answer I realized I didn't show 1st column as OP requested... Fixing... Thanks +1. –  a1ex07 Dec 14 '12 at 23:31
add comment

Not performant , but it seems to return what you want :

select 
 --d.id1, 
--little update - I realized d.id1 doesn't show 1st column doesn't show values
-- as in example, so here is the fix: 
CASE 
  WHEN COUNT(*) >1 THEN d.id1  
  ELSE d.id 
END as id,
CASE 
WHEN COUNT(*) >1 THEN 'concatenated'
ELSE NULL
END AS `type`,
GROUP_CONCAT(
case 
when val = 'start' or val = 'end' then null
else val 
end
order by id
) as diary

FROM
(
SELECT x.id, x.val,
CASE WHEN start_id IS NULL OR end_id IS NULL OR start_id > end_id THEN id
ELSE start_id
END as id1,
end_id
FROM 
(
    select a.*,
    (select max(id) from diary b where b.val ='start' and b.id <=a.id) as start_id,
    (select min(id) from diary b where b.val ='end' and b.id >=a.id) as end_id

     from diary a
 )x  
)d
group by id1,end_id;

Update See comments in the code

share|improve this answer
    
I tested your query against my sample data and it works !!! (See the CAVEAT in my answer). +1 for getting your answer in first. –  RolandoMySQLDBA Dec 14 '12 at 22:54
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.