locked
how to split column to two column RRS feed

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote
    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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