컬럼이 5줄인 데이터를 한줄로 보여지게 하는 방법

CREATE TABLE Code_Row (

idx int not null auto_increment,

code1 varchar(10) not null,

code2 varchar(20) not null,

code3 varchar(30) not null,

code4 varchar(30) not null,

code5 varchar(30) not null,

primary key (idx)

) engine=innodb charset=utf8mb4;

insert into Code_Row (code1, code2, code3, code4, code5) Values 

('0001', '0002', '0003', '0004', '0005'), ('0006', '0007', '0008', '0009', '0010')

, ('0011', '0012', '0013', '0014', '0015'), ('0016', '0017', '0018', '0019', '0020')

, ('0021', '0022', '0023', '0024', '0025'), ('0026', '0027', '0028', '0029', '0030')

, ('0031', '0032', '0033', '0034', '0035'), ('0036', '0037', '0038', '0039', '0040');

select * from Code_Row;

select code1, @x1 := @x1 + 1 as seq1,

code2, @x1 := @x1 + 1 as seq2,

code3, @x1 := @x1 + 1 as seq3,

code4, @x1 := @x1 + 1 as seq4,

code5, @x1 := @x1 + 1 as seq5

from Code_Row, (select @x1 := 0) x1

CREATE TABLE seq_num (num int not null auto_increment, primary key (num));

delimiter //

create procedure `num_sp` ()

begin

declare idx int default 1;

while idx < 51 do

insert into seq_num values (idx);

set idx = idx + 1;

end while;

end //

delimiter ;

call num_sp();

select * from seq_num;

explain

select if(x.seq1 = y.num, code1, '') as code1,

if(x.seq2 = y.num, code2, '') as code2,

if(x.seq3 = y.num, code3, '') as code3,

if(x.seq4 = y.num, code4, '') as code4,

if(x.seq5 = y.num, code5, '') as code5

from (

select code1, @x1 := @x1 + 1 as seq1,

code2, @x1 := @x1 + 1 as seq2,

code3, @x1 := @x1 + 1 as seq3,

code4, @x1 := @x1 + 1 as seq4,

code5, @x1 := @x1 + 1 as seq5

from Code_Row, (select @x1 := 0) x1

) x inner join seq_num y

on x.seq1 = y.num or x.seq2 = y.num or x.seq3 = y.num or x.seq4 = y.num or x.seq5 = y.num

select concat(code1, code2, code3, code4, code5) as code

from (

select if(x.seq1 = y.num, code1, '') as code1,

if(x.seq2 = y.num, code2, '') as code2,

if(x.seq3 = y.num, code3, '') as code3,

if(x.seq4 = y.num, code4, '') as code4,

if(x.seq5 = y.num, code5, '') as code5

from (

select code1, @x1 := @x1 + 1 as seq1,

code2, @x1 := @x1 + 1 as seq2,

code3, @x1 := @x1 + 1 as seq3,

code4, @x1 := @x1 + 1 as seq4,

code5, @x1 := @x1 + 1 as seq5

from Code_Row, (select @x1 := 0) x1

) x inner join seq_num y

on x.seq1 = y.num or x.seq2 = y.num or x.seq3 = y.num or x.seq4 = y.num or x.seq5 = y.num

) t

where code1 is not null

and code2 is not null

and code3 is not null

and code4 is not null

and code5 is not null;