SQLite 3.39

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table table_name (
  session_id int,
  item text)
✓
insert into table_name values 
(1,	't-shirt'),
(1,	'trousers'),
(1,	'hat'),
(3,	'belt'),
(2,	'shoes')
✓
with cte as
(
  select *,
    count(*) over (partition by session_id) cnt
  from table_name 
)
select a.session_id,
       a.item item_a,
       b.item item_b
from cte a left join cte b
on a.session_id = b.session_id
   and a.item > b.item
where (a.cnt > 1 and b.item is not null) or
      (a.cnt = 1)
order by a.session_id, a.item


session_id
item_a
item_b


1
t-shirt
hat

1
trousers
hat

1
trousers
t-shirt

2
shoes
null

3
belt
null

session_id	item_a	item_b
1	t-shirt	hat
1	trousers	hat
1	trousers	t-shirt
2	shoes	null
3	belt	null