How to merge n+1 columns in pairs?

Question

MySQL

Hello!
There is a table, there are a lot of columns in it, I don’t understand how to combine them in pairs.

CREATE TABLE rasp (
    `gname` VARCHAR(58) CHARACTER SET utf8,
    `gid` VARCHAR(7) CHARACTER SET utf8,
    `1pon` VARCHAR(58) CHARACTER SET utf8,
    `1pon_n` VARCHAR(22) CHARACTER SET utf8,
    `2pon` VARCHAR(51) CHARACTER SET utf8,
    `2pon_n` VARCHAR(26) CHARACTER SET utf8,
    `3pon` VARCHAR(80) CHARACTER SET utf8,
    `3pon_n` VARCHAR(34) CHARACTER SET utf8,
    `4pon` VARCHAR(120) CHARACTER SET utf8,
    `4pon_n` VARCHAR(42) CHARACTER SET utf8,
    `5pon` VARCHAR(90) CHARACTER SET utf8,
    `5pon_n` VARCHAR(43) CHARACTER SET utf8,
    `6pon` VARCHAR(80) CHARACTER SET utf8,
    `6pon_n` VARCHAR(53) CHARACTER SET utf8,
    `7pon` VARCHAR(57) CHARACTER SET utf8,
    `7pon_n` VARCHAR(47) CHARACTER SET utf8,

As a result, I want the data, for example, from 1pon_n to be transferred to 1pon, 2pon_n to 2pon, respectively.
I know that there is concat (Google enlightened me), but I don’t need to skeleton every time I request a database, I need a ready-made version on the server.
The problem is that instead of pon I also have sreda, cht.. (abbreviated days of the week).
insert query:

INSERT INTO rasp VALUES ('ПРИКЛАДНАЯ МАТЕМАТИКА И ИНФОРМАТИКА','9611','ЯиМП 1006 Марченко','','А и Г 1008 Ануфриева','','ч/нАлгебра и геометрия 216 Карчевский Е.М.','н/нМатематический анализ 216 Лапин','Иностранный язык  Хакимзянова Д. 404, Саляхова 309, Баранова 507, Сабирова 511, Яхин 801, Сигачева 312, Сайфуллина 313  ','','','','','','','','','МА 1113 Тихонов','','Математический анализ 216 Лапин','','','','','','','','','','','','','н/нЯзыки и методы программирования 1211 Гайнутдинова','ч/нА и Г 1006 Ануфриева','Иностранный язык  Хакимзянова Д. 511, Саляхова 309, Баранова 507, Сабирова 404, Яхин 801, Сигачева 904, Сайфуллина 404 ф/к  ','','Алгебра и геометрия 216 Карчевский Е.М.','','Лаб. пр-кум 1206 Марченко','','','','','','','','','','','','','','','','','','','','','','','','','','Языки и методы программирования 1211 Гайнутдинова','','МА 905 Тихонов','','Уч. пр-ка 1206 Марченко','','','','','','','','','Архитектура компьютеров 1113 Кадыров','','Иностранный язык  Хакимзянова Д. 404, Саляхова 801, Баранова 511, Сабирова 409, Яхин 904, Сигачева 406, Сайфуллина 312  ','','','','','','','','','','','');
INSERT INTO rasp VALUES ('ПРИКЛАДНАЯ МАТЕМАТИКА И ИНФОРМАТИКА','9611','ЯиМП 1114 Зиятдинов','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','Уч. пр-ка 910 Зиятдинов','','','','','','','','','','','','','','','','','','','','','','','','','','','','','','Лаб. пр-кум 910 Зиятдинов','','','','','','','','','','','','','','','','','','','','','','');

You also need to glue the lines with the same gid, they are not in order, that is, it can be 9611 and the next 9742. There are at least 2 lines with the same gid (there are 2 and 3 more)

Answer 1

1. You can transfer data from one column to another using the instruction UPDATE, but you will have to describe all the columns by hand, or write a separate script in another language that would generate this code as a string:

UPDATE rasp
SET
    1pon = CONCAT(1pon, ' ', 1pon_n),
    2pon = CONCAT(2pon, ' ', 2pon_n),
    ...

2. To eliminate duplicates, the first step is to add a primary unique key to the table (let's call it unique_id). The easiest way to delete them is to use a program in an imperative programming language (like php, c#, python or any other suitable one) that unloads the records, analyzes them and deletes them. A pure SQL solution is possible, but due to the unknown number of duplicates, it will either be very complex or have to be run multiple times.