Merging and consolidating rows by values based on date in SQL Server

3htmauhk  于 2023-03-22  发布在  SQL Server
关注(0)|答案(1)|浏览(142)

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 IDdatestatus-in1TIMEstatus-out1TIMEstatus-in2TIMEstatus-out2TIMEstatus-in3TIMEstatus-out3TIMETotal
24812/06/22110:003319:5009:50

449 DriverB

DRIVER IDdatestatus-in1TIMEstatus-out1TIMEstatus-in2TIMEstatus-out2TIMEstatus-in3TIMEstatus-out3TIMETotal
44913/06/22215:102216:1001: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

41zrol4v

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:

SELECT 
    [DRIVER ID],
    [date],
    [1] AS [status-in1],
    [out1] AS [status-out1],
    [2] AS [status-in2],
    [out2] AS [status-out2],
    [3] AS [status-in3],
    [out3] AS [status-out3],
    [total]
FROM (
    SELECT 
        [DRIVER ID],
        [date],
        'in' + CAST([status] AS VARCHAR) AS [in_out],
        [time]
    FROM 
        Parking
    WHERE 
        [status] IN (1, 2, 3)
    UNION ALL
    SELECT 
        [DRIVER ID],
        [date],
        'out' + CAST([status] AS VARCHAR) AS [in_out],
        [time]
    FROM 
        Parking
    WHERE 
        [status] IN (11, 22, 33)
) AS SourceTable
PIVOT (
    MAX([time])
    FOR [in_out] IN ([in1], [out1], [in2], [out2], [in3], [out3])
) AS PivotTable
CROSS APPLY (
    SELECT 
        ISNULL(SUM(DATEDIFF(MINUTE, [in1], [out1])), 0) + 
        ISNULL(SUM(DATEDIFF(MINUTE, [in2], [out2])), 0) + 
        ISNULL(SUM(DATEDIFF(MINUTE, [in3], [out3])), 0) AS [total]
) AS TotalTime
WHERE 
    [DRIVER ID] = '516'
ORDER BY 
    [date]

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.

相关问题