Merge two tables in horizontal without common column in Mysql

Question

I am practising with sakila database in MySQL. There is a table name actor with a column last_name. I want to list all last_name which is not repeated in first column, and repeated in second column.

Input:

-- auto-generated definition
create table actor
(
    actor_id    smallint unsigned auto_increment
        primary key,
    first_name  varchar(45)                         not null,
    last_name   varchar(45)                         not null,
    last_update timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP
)
    charset = utf8;

create index idx_actor_last_name
    on actor (last_name);

insert into actor (actor_id, first_name, last_name, last_update)
values  (1, 'PENELOPE', 'GUINESS', '2006-02-15 04:34:33'),
        (2, 'NICK', 'BERGEN', '2006-02-15 04:34:33'),
        (3, 'ED', 'BERRY', '2006-02-15 04:34:33'),
        (4, 'JENNIFER', 'DAVIS', '2006-02-15 04:34:33'),
        (5, 'JOHNNY', 'OLIVIER', '2006-02-15 04:34:33'),
        (6, 'BETTE', 'GABLE', '2006-02-15 04:34:33'),
        (7, 'GRACE', 'MOSTEL', '2006-02-15 04:34:33'),
        (8, 'MATTHEW', 'GABLE', '2006-02-15 04:34:33'),
        (9, 'JOE', 'SWANK', '2006-02-15 04:34:33'),
        (10, 'CHRISTIAN', 'GABLE', '2006-02-15 04:34:33'),
        (11, 'ZERO', 'CAGE', '2006-02-15 04:34:33'),
        (12, 'KARL', 'BERRY', '2006-02-15 04:34:33'),
        (13, 'UMA', 'WOOD', '2006-02-15 04:34:33'),
        (14, 'VIVIEN', 'BERGEN', '2006-02-15 04:34:33'),
        (15, 'CUBA', 'OLIVIER', '2006-02-15 04:34:33');

Expected output: In the result, if a column has more rows than the rest, data in the rest column will be null

not_repeated	repeated
CAGE	BERGEN
DAVIS	BERRY
GUINESS	GABLE
MOSTEL	OLIVIER
SWANK	null
WOOD	null

I have a solution with row_number() function. Is there any other solution to resolve this problem? Hope someone can help me. Thank you in advance!

WITH group_last_name AS
         (SELECT last_name,
                 CASE
                     WHEN count(*) > 1 THEN 'many'
                     ELSE 'once'
                     END AS frequency
          FROM actor
          GROUP BY last_name),
     onceTbl AS
         (SELECT last_name,
                 row_number() OVER (PARTITION BY frequency) AS idx
          FROM group_last_name
          WHERE frequency = 'once'),
     manyTbl AS
         (SELECT last_name,
                 row_number() OVER (PARTITION BY frequency) AS idx
          FROM group_last_name
          WHERE frequency = 'many')
SELECT onceTbl.last_name AS not_repeated,
       manyTbl.last_name AS repeated
FROM manyTbl
         LEFT OUTER JOIN onceTbl ON onceTbl.idx = manyTbl.idx
UNION
SELECT onceTbl.last_name AS not_repeated,
       manyTbl.last_name AS repeated
FROM onceTbl
         LEFT OUTER JOIN manyTbl ON onceTbl.idx = manyTbl.idx;

Answer 1

you could allocate the column number and row number at the same time, to cope with nulls select distinct row numbers finally joining the distinct row numbers and using conditional aggregation output to separate columns

with cte as
(
select last_name, count(*) ,
        case when count(*) >1 then 2 else 1 end columnno,
        row_number() over (order by last_name) rn
from actor a
group by last_name having count(*) = 1
union all
select last_name, count(*) ,
        case when count(*) >1 then 2 else 1 end columnno,
        row_number() over (order by last_name) rn
from actor a
group by last_name having count(*) > 1
)
,cte1 as
(select distinct rn from cte)
select max(case when columnno = 1 then last_name else null end) onlyone,
       max(case when columnno = 2 then last_name else null end) morethanone
from cte1 
join cte on cte.rn = cte1.rn
group by cte1.rn
order by cte1.rn
;

https://dbfiddle.uk/Y9Tq1OhT