Merging rows in MySQL with the same item number but different dates

Question

good day everyone, i would like to ask you guys for help. see i have a table Table1 with columns

Item_id, 
date, 
count

what i need is a MySQL query that will: query conditions *all items with the same id should be merged in one row *sort dates from earliest to latest into one row.

thank you and have a great day.

Table1

Item_id||Date      |Count
1       |01-20-2017|    12
1       |02-02-2017|    5
1       |03-01-2017|    10
2       |03-01-2017|    45
3       |01-26-2017|    15
1       |04-09-2017|    12
2       |04-25-2017|    5
3       |02-15-2017|    45

Table2

Item_id    Date1    Count1     Date2    Count2     Date3    Count3     Date4    Count4
1      01-20-2017      12    02-02-2017     5   03-01-2017    10   04-09-2017   
2      03-01-2017      45    04-25-2017     5   
3      01-26-2017      15    02-15-2017     45

enter image description here

To pull this off in MySQL, you'll need to use dynamic SQL, which is probably way above your (and my) pay grade. It would be much easier to generate this output in your presentation layer (PHP?). By the way, why do you need this output? — Tim Biegeleisen, Oct 11 '18 at 08:37
If you can add your sample data and expected output as text to the question I'll give you an answer. — P.Salmon, Oct 11 '18 at 08:41
You might need something like `SELECT Item_id, GROUP_CONCAT(Date) FROM Table1 GROUP BY Item_id`. But you still need to do something after that in your application. Like Tim(?) I also fail to see to intent of this output if you're only making a simple list without any additional COUNT() etc. functions. You might end up with a bunch of empty columns (like now with other than Item_id 1). — ZZ-bb, Oct 11 '18 at 08:52
now that i think about it, the expected output doesn't make sense. LOL. actually this problem was just relayed to me. the gist is that to compute the difference in days between every transaction. — Lloyd Bislig Abao, Oct 11 '18 at 09:25
Possible duplicate of [MySQL pivot table](https://stackoverflow.com/questions/7674786/mysql-pivot-table) — Madhur Bhaiya, Oct 11 '18 at 09:36
Originally i was planning to use `datediff` to get results after merging them. — Lloyd Bislig Abao, Oct 11 '18 at 09:54

score 0 · Accepted Answer · answered Oct 11 '18 at 12:11

set @sql = 
(
select  concat
            ('Select item_id,',
            group_Concat(maxs)
            ,' from '
            , '(select t.*,if(item_id <> @p ,@col:=1,@col:=@col+1) col,
                @p:= item_id
             from t cross join (select @col:=0,@p:=0) c
             order by item_id,date) c
             group by item_id;'
            ) 
from
(
select 
         concat
         (
         'max(case when col  = ', col, ' then date else null end) date',col,
         ','
         'max(case when col  = ', col, ' then count else null end) count',col
         ) as maxs

from
(
select @col:=@col + 1 col
from t
cross join (select @col:=0) c
where item_id = 
(
select item_id from
(
select item_id, count(*) col from t group by item_id order by count(*) desc limit 1
) s
)
) x
) y

);

Here the item_id with the most dates is used to build the required number of aggregation statements and wrapped in a select..group by

@sql can then used in a prepared statement

prepare sqlstmt from @sql;
execute sqlstmt;
deallocate prepare sqlstmt;

to give this result

+---------+------------+--------+------------+--------+------------+--------+------------+--------+
| item_id | date1      | count1 | date2      | count2 | date3      | count3 | date4      | count4 |
+---------+------------+--------+------------+--------+------------+--------+------------+--------+
|       1 | 2017-01-20 |     12 | 2017-02-02 |      5 | 2017-03-01 |     10 | 2017-04-09 |     12 |
|       2 | 2017-03-01 |     45 | 2017-04-25 |      5 | NULL       |   NULL | NULL       |   NULL |
|       3 | 2017-01-26 |     15 | 2017-02-15 |     45 | NULL       |   NULL | NULL       |   NULL |
+---------+------------+--------+------------+--------+------------+--------+------------+--------+
3 rows in set (0.00 sec)

Merging rows in MySQL with the same item number but different dates

1 Answers1