CODEWITHSUNDEEP
sqlmysqlunionunpivot
srd
srd

Reputation: 51

MySQL: Unpivot specific columns into 2 output columns

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

Upvotes: 0

Views: 323

Answers (1)

SelVazi
SelVazi

Reputation: 16043

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

Upvotes: 1

Related Questions