Sqlite. Table unpivot-CodePudding

I have a table & want to make some data processing (convert it from 4 columns to 2):

So, desired output will looks like below:

cue        relation
although   neverthel...
although   yet
although   but
deal       no 
deal       cards
deal       shake
music      notes
music      band
music      rhythm

Does it possible by using SQLITE engine? Thanks.

CodePudding user response：

You can join to numbers, then use a CASE WHEN for the relation column.

select *
from
(
  select cue
  , case column1
    when 1 then R1
    when 2 then R2
    when 3 then R3
    end relation
  from your_table t
  cross join (values (1),(2),(3)) n
) q
where relation is not null

CodePudding user response：

you can create new table that will consist of data as you wish. I called your table as : origin_table and the new one as: two_col_table

create table two_col_table (cue varchar2(22), relation varchar2(22));

insert into two_col_table(cue, relation) SELECT cue, r1 FROM origin_table;
insert into two_col_table(cue, relation) SELECT cue, r2 FROM origin_table;
insert into two_col_table(cue, relation) SELECT cue, r3 FROM origin_table;

SELECT * FROM two_col_table ORDER BY CUE;