Show same record in single row and in another column

51
October 09, 2018, at 00:00 AM

I have data in this format

userid         date        time
1           2018-09-04     07:23:00
1           2018-09-05     06:24:00
1           2018-09-06     09:23:00
1           2018-09-06     06:15:00
1           2018-09-07     09:16:24
1           2018-09-07     06:22:00

How we can show data in following format through mysql query?

Required Output:

userid         date        time1         time2    
1           2018-09-04     07:23:00       NULL
1           2018-09-05     06:24:00       NULL
1           2018-09-06     09:23:00     06:15:00     
1           2018-09-07     09:16:00     06:22:00
Answer 1

It ain't pretty, and it makes some assumptions about your data set, but here's one idea...

DROP TABLE IF EXISTS my_table;
CREATE TABLE my_table
(userid INT NOT NULL
,dt DATETIME NOT NULL
,PRIMARY KEY(userid,dt)
);
INSERT INTO my_table VALUES
(1,'2018-09-04 07:23:00'),
(1,'2018-09-05 06:24:00'),
(1,'2018-09-06 09:23:00'),
(1,'2018-09-06 06:15:00'),
(1,'2018-09-07 09:16:24'),
(1,'2018-09-07 06:22:00');
SELECT x.userid
     , MIN(x.dt) x_dt
     , y.dt y_dt 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.userid = x.userid 
   AND DATE(y.dt) = DATE(x.dt) 
   AND y.dt > x.dt 
 GROUP  
    BY DATE(x.dt);
+--------+---------------------+---------------------+
| userid | x_dt                | y_dt                |
+--------+---------------------+---------------------+
|      1 | 2018-09-04 07:23:00 | NULL                |
|      1 | 2018-09-05 06:24:00 | NULL                |
|      1 | 2018-09-06 06:15:00 | 2018-09-06 09:23:00 |
|      1 | 2018-09-07 06:22:00 | 2018-09-07 09:16:24 |
+--------+---------------------+---------------------+
READ ALSO
SQL command to tally results over multiple columns?

SQL command to tally results over multiple columns?

Consider the following SQL table titled 'calls' which is a complete list of calls from John's phone bill:

44
Mysql concat unequal table field

Mysql concat unequal table field

why I can't Select a row of a table with an unequal concat construction? I'll show you an example

61
Filtering a value inside a from subquery

Filtering a value inside a from subquery

This is part of a longer queryI am trying to return the name of a team and the total count

62