MySQL: Unpivot specific columns into 2 output columns

Question

I have the following table:

id	start_1	start_2	end_1	end_2
1	day	night	night	day
2	night	night	day	night

How can I UNPIVOT to get some of the values in one column, and some in another?

Like this:

id	start	end
1	day	night
1	night	night
2	night	day
2	night	night

I found solutions to return all the values in one column, but I'm unable to convert it to two columns.

select t.id,
  s.start_orig,
  case s.start_orig
    when 'start_1' then start_1
    when 'start_2' then start_2
  end as start,
   e.end_orig,
  case e.end_orig
    when 'end_1' then end_1
    when 'end_2' then end_2
  end as end
from table t
cross join
(
  select 'start_1' as start_orig
  union all select 'start_2'
) s
cross join
(
  select 'end_1' as end_orig
  union all select 'end_2'
) e

query from: MySQL - How to unpivot columns to rows?

But then I get start_n * end_n rows, which is too many

id	start_orig	start	end_orig	end
1	start_1	day	end_1	night
1	start_1	night	end_2	night
1	start_2	night	end_1	day
1	start_2	night	end_2	night
2	start_1	day	end_1	night
2	start_1	night	end_2	night
2	start_2	night	end_1	day
2	start_2	night	end_2	night

SelVazi · Accepted Answer · 2023-02-11 23:42:41Z

1

Using only one CROSS JOIN can do the job :

  with cte as (
  select t.id, s.start_orig,
    case s.start_orig
      when 'start_1' then start_1
      when 'start_2' then start_2
    end as start, s.end_orig,
    case s.end_orig
      when 'end_1' then end_1
      when 'end_2' then end_2
    end as end
  from mytable t
  cross join (
    select 'start_1' as start_orig, 'end_1' as end_orig
    union all select 'start_2', 'end_2'
  ) s
  order by id
)
select id, start, end
from cte;

Demo here

edited Feb 11, 2023 at 23:42

answered Feb 11, 2023 at 23:15

SelVazi

16.2k2 gold badges20 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: Unpivot specific columns into 2 output columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related