I have a table with driver number, date, state and time. I want to merge the rows with the same date and divide by driver number. and divide the time in the same line but according to the status Each status will get its own column The distribution of the status is according to numbers:
This is a table (Table name = Parking):
| DRIVER ID | date | status | TIME |
| ------------ | ------------ | ------------ | ------------ |
| 516 | 11/06/22 | 1 | 09:00 |
| 516 | 11/06/22 | 11 | 12:30 |
| 516 | 12/06/22 | 1 | 08:30 |
| 516 | 12/06/22 | 11 | 14:10 |
| 248 | 11/06/22 | 3 | 10:00 |
| 449 | 13/06/22 | 2 | 15:10 |
| 449 | 13/06/22 | 22 | 16:30 |
| 248 | 11/06/22 | 33 | 19:50 |
| 516 | 12/06/22 | 2 | 21:20 |
| 516 | 12/06/22 | 22 | 23:20 |
I need the division of the status in this way:
1 - in1
11 - Out1
2 - in2
22 - out2
3 - in3
33 - out3
Each driver will receive a table on his own, but only to export in CSV. All on the same CSV file. I don't need to input it in a new table or create a table
516-DriverA
| DRIVER ID | date | status-in1 | TIME | status-out1 | TIME | status-in2 | TIME | status-out2 | TIME | status-in3 | TIME | status-out3 | TIME | Total |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 516 | 11/06/22 | 1 | 09:00 | 11 | 12:30 | | | | | | | | | 03:30 |
| 516 | 12/06/22 | 1 | 08:30 | 11 | 14:10 | 2 | 21:20 | 22 | 23:20 | | | | | 07:40 |
248 DriverB
DRIVER ID | date | status-in1 | TIME | status-out1 | TIME | status-in2 | TIME | status-out2 | TIME | status-in3 | TIME | status-out3 | TIME | Total |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
248 | 12/06/22 | 1 | 10:00 | 33 | 19:50 | 09:50 |
449 DriverB
DRIVER ID | date | status-in1 | TIME | status-out1 | TIME | status-in2 | TIME | status-out2 | TIME | status-in3 | TIME | status-out3 | TIME | Total |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
449 | 13/06/22 | 2 | 15:10 | 22 | 16:10 | 01:00 |
I tried several ways, including a number of queries but I could not achieve this result
Hope this is doable, Thank you.
I checked some queries, tried to combine some options. But I couldn't get the result I want
1条答案
按热度按时间41zrol4v1#
You can use the SQL PIVOT function to pivot the "status" column and convert each status value into a separate column with its corresponding "time" value. You can also use the SQL aggregate function SUM to calculate the total time for each driver and date.
Here is an example query that should work for your table structure:
This query will pivot the "status" column and create separate columns for each status value with its corresponding "time" value. It will also calculate the total time for each driver and date using the SUM and DATEDIFF functions to calculate the difference between the in and out time in minutes.
Note: You can change the driver ID in the WHERE clause to get the results for a different driver. Also, you can modify the column names and formatting to match your desired output. Finally, you can export the results to a CSV file using your database management tool or a programming language.