locked
how to split column to two column RRS feed

  • Question

  • User-1634604574 posted

    i have this table  tb1

    log type                       time

    in                                  2020-07-20 07:30:00.000

    out                                2020-07-20 09:10:00.000

    out                                2020-07-20 13:30:00.000

    in                                  2020-07-20 10:00:00.000

    i want  to get this result

    time_in                                               time_out

    2020-07-20 07:30:00.000                   2020-07-20 09:10:00.000

     2020-07-20 10:00:00.000                 2020-07-20 13:30:00.000

    Monday, July 20, 2020 7:15 PM

Answers

  • User-1330468790 posted

    Hi zhyanadil.it@gmail.com,

     

    I would suggest you use the simplest way to order the time and use CASE WHEN to split the time field into two columns. That way, there is no need to match the in to out and the output will be always the same.

    You could refer to below sql statement.

    DROP TABLE IF EXISTS #TempTable 
    CREATE TABLE #TempTable (
    [log type] NVARCHAR(50),
    [time] DATETIME
    )
    
    INSERT INTO #TempTable ([log type],[time]) VALUES ('in', '2020-07-20 07:30:00.000')
    INSERT INTO #TempTable ([log type],[time]) VALUES ('out', '2020-07-20 09:10:00.000')
    INSERT INTO #TempTable ([log type],[time]) VALUES ('out', '2020-07-20 13:30:00.000')
    INSERT INTO #TempTable ([log type],[time]) VALUES ('in', '2020-07-20 10:00:00.000')
    
    
    SELECT 
    	CASE WHEN [log type] = 'in' THEN [time]  END AS [time_in],
    	CASE WHEN [log type] = 'out' THEN [time]  END AS [time_out]
    FROM #TempTable 
    ORDER BY [time]
    
    
    DROP TABLE #TempTable 

    Result:

     

    However, if you could be sure about the match between the in and out time, you could use below sql statement to get what you want.

    DROP TABLE IF EXISTS #TempTable 
    CREATE TABLE #TempTable (
    [log type] NVARCHAR(50),
    [time] DATETIME
    )
    
    INSERT INTO #TempTable ([log type],[time]) VALUES ('in', '2020-07-20 07:30:00.000')
    INSERT INTO #TempTable ([log type],[time]) VALUES ('out', '2020-07-20 09:10:00.000')
    INSERT INTO #TempTable ([log type],[time]) VALUES ('out', '2020-07-20 13:30:00.000')
    INSERT INTO #TempTable ([log type],[time]) VALUES ('in', '2020-07-20 10:00:00.000')
    
    
    ;WITH cte1 AS (
    SELECT a.[time_in],  Row_number() Over(Order by a.[time_in]) AS in_id FROM (
    SELECT CASE WHEN [log type] = 'in' THEN [time]  END AS [time_in] FROM #TempTable) a
    WHERE a.[time_in] IS NOT NULL
    ) ,
    cte2 AS (
    SELECT b.[time_out],  Row_number() Over(Order by b.[time_out]) AS out_id  FROM (
    SELECT CASE WHEN [log type] = 'out' THEN [time]  END AS [time_out] FROM #TempTable) b
    WHERE b.[time_out] IS NOT NULL
    )
    
    SELECT 
    	t1.[time_in], t2.[time_out]
    	
    FROM cte1 t1
    -- you could use Left Join if the number of rows of in-time is always more than that of out-time JOIN cte2 t2 on t1.in_id = t2.out_id DROP TABLE #TempTable

    Result:

     

    Hope this can help you.

    Best regards,

    Sean

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, July 21, 2020 3:07 AM