0

Can you help me transform this data using query?

id datetime status
1 2022-04-01 07:38:31 Time In
1 2022-04-01 12:07:17 Break In
1 2022-04-01 12:07:39 Break Out
1 2022-04-01 16:43:17 Time Out
1 2022-04-02 07:38:31 Time In
1 2022-04-02 12:07:39 Break Out
id date Time In Break In Break Out Time Out
1 2022-04-01 07:38:31 12:07:17 12:07:39 16:43:17
1 2022-04-02 07:38:31 12:07:39
1 2022-04-03
1 2022-04-04
1 2022-04-05
1 2022-04-06

The table date should be up to 2022-04-30.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
warlock
  • 3
  • 3
  • You can check out [question containing dynamic pivot samples](https://stackoverflow.com/questions/tagged/sql-server%2bdynamic-pivot?tab=Frequent) and consider conditional logic. – Barbaros Özhan May 03 '22 at 11:04
  • Can it also be done in Excel Pivot? – warlock May 03 '22 at 11:34
  • I don't know how it can be done in Excel's macroes... – Barbaros Özhan May 03 '22 at 11:44
  • Each make and version of database server has its own dialect of SQL. Some have pivot operations and others do not. Please [edit] your question to add a [tag](//stackoverflow.com/help/tagging) for the database you use. [tag:postgresql]? [tag:mysql]? [tag:sql-server]? [tag:oracle]? [tag:google-bigquery]? [tag:amazon-redshift]? Another? – O. Jones May 03 '22 at 12:12

1 Answers1

0

You'll need to make the date function correct for the type of SQL you're using, this is T-SQL (MS SQL Server) code:

SELECT ID, 
       convert(date,Datetime,111) as Day,
       MAX(CASE WHEN status = 'Time In' THEN Datetime ELSE NULL END) as TimeIn,
       MAX(CASE WHEN status = 'Break In' THEN Datetime ELSE NULL END) as BreakIn,
       MAX(CASE WHEN status = 'Break Out' THEN Datetime ELSE NULL END) as BreakOut,
       MAX(CASE WHEN status = 'Time Out' THEN Datetime ELSE NULL END) as TimeOut
 FROM mytest
GROUP BY ID, 
         convert(date,Datetime,111)

A suggestion: consider changing the name of the datetime column - giving a column a reserved word as a column name will come back to haunt you at some point.

Also, if you want just the time portion, then apply whatever logic is appropriate to your SQL flavor to parse that from the Datetime.

Bruce
  • 146
  • 4