how to categorize a column into two columns

Question

I have table like this:

ID	Type	Time
Q001	In	2022-8-30 08:30:00
Q001	Out	2022-8-30 10:35:00
Q001	In	2022-8-30 10:42:00
Q001	Out	2022-8-30 12:15:00

I want to get result like this:

ID	In	out
Q001	2022-8-30 08:30:00	2022-8-30 10:35:00
Q001	2022-8-30 10:42:00	2022-8-30 12:15:00

I think maybe I can use two sub-tables for in and out data ordered by id and time,then create a table with same index of each table,like below

 select  Intable.id,
         Intable.time,
         Outtable.time 
 from (select * from T1
       where type='IN' order by ID, Time  as Intable) 
 join ( select * from T1
         where type='out' as Outtable order by ID, Time) 
 where Intable.ID = Outtable.id

Is this possible? And how do I get from each table with same index? Thx.

Does this answer your question? [Convert Rows to columns using 'Pivot' in SQL Server](https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server) — Stu, Aug 29 '22 at 19:04

score 2 · Answer 1 · answered Aug 29 '22 at 20:25

To get your desired results you can use a query like this

SELECT
    id,
    MIN(time) AS "in",
    MAX(time) AS out
FROM (
    SELECT
        *,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY time) rn
    FROM t1
) t
GROUP BY id, rn

Or another

SELECT
    id,
    (SELECT 
         MAX(time) 
     FROM t1 t_in
     WHERE t_in.id = t1.id 
           AND t_in.type = 'In'
           AND t_in.time < t1.time
    ) "in",
    time "out"
FROM t1
WHERE type = 'Out'

Both queries produces the same results

id	in	out
Q001	2022-08-30 08:30:00.000	2022-08-30 10:35:00.000
Q001	2022-08-30 10:42:00.000	2022-08-30 12:15:00.000

db<>fiddle here

how to categorize a column into two columns

1 Answers1