Question 20224 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql split column into multiple columns based on text language or number of rows

I have a column that look like the following

id | Text    
1  | English text1    
2  | English text2    
3  | English text3    
4  | English text4

5  | French text1    
6  | French text2    
7  | French text3    
8  | French text4

9  | Arabic text1    
10 | Arabic text2    
11 | Arabic text3    
12 | Arabic text4

etc…

I want to make like this

id | EngText            FreText          ArbText    
1  | English text1    French text1    Arabic text1    
2  | English text2    French text2    Arabic text2    
3  | English text3    French text3    Arabic text3    
4  | English text4    French text4    Arabic text4

Using phpMyAdmin if possible or MySQL query. By either detecting the text language or the number of id where each 4 rows will be in different column.

I used 4 just as an example but they are actually more than that where each language has the same number of rows.

Thank you

Tags:

4

Answers


  1. 0

    Realize that if you do it this way, you will have to change the database structure if you add or remove a language. This is not good practice.

    I understand the urge to do it this way but it is not good practice. Unless you are really very very sure never to have to add or remove a language (are you ever in this world?), go for a table structure where you have a table with an unique id (autonumber), textId, languageId (textId+languageId together unique) and the text in that language. That is basic structural database design.

    If you are convinced and want to do it that way, you just need to copy the original table into the new one, with

    INSERT INTO newTable (id, textId, languageId, text)
SELECT NULL, mod(id-1, <nr of languages>)+1, FLOOR((id-1)/<nr of languages>)+1, text
FROM oldTable

    Next you make a language table to define the languages based on the numbers you get in the result, with columns id, language (VARCHAR)

    INSERT INTO languages (id, language)
SELECT DISTINCT languageId, 'Fill in language name manually'
FROM newTable

    (Note: I couldn’t post this as a remark as it was too long. Posted a solution that answers the question in the way that the OP wanted (even though bad general practice) as well.)

    Login or Signup to reply.
  2. 0

    You can do it as follows :

    select max(case when id/4 <= 1 then Text end) as 'EngText',
        max(case when (id/4 <= 2 and id/4 > 1) then Text end) as 'FreText',
        max(case when (id/4 <= 3 and id/4 > 2) then Text end) as 'ArbText'
from mytable
group by id mod 4

    id/4 will result a number from 0 to 1 for english, 1 to 2 for french and 2 to 3 from arabic, so we can group data by each language using max() case when clause.

    id mod 4 will help to group data side by side .

    An other way to do it :

    select max(case when (id - 1) div 4 = 0 then Text end) as 'EngText',
        max(case when (id - 1) div 4 = 1 then Text end) as 'FreText',
        max(case when (id - 1) div 4 = 2 then Text end) as 'ArbText'
from mytable
group by id mod 4

    Demo here

    Login or Signup to reply.
  3. 0

    I strongly advise against your current approach but this query will provide the result you asked for:

    SELECT l1.id, l1.Text AS en, l2.Text AS fr, l3.Text AS ar
FROM (SELECT COUNT(*) DIV 3 AS r FROM lang) v
JOIN lang l1 ON l1.id <= v.r
JOIN lang l2 ON l2.id = l1.id + v.r
JOIN lang l3 ON l3.id = l2.id + v.r;

    or using the pivot approach:

    SELECT
    MAX(IF((id - 1) DIV v.r = 0, id, NULL)) AS id,
    MAX(IF((id - 1) DIV v.r = 0, Text, NULL)) AS en,
    MAX(IF((id - 1) DIV v.r = 1, Text, NULL)) AS fr,
    MAX(IF((id - 1) DIV v.r = 2, Text, NULL)) AS ar
FROM lang
JOIN (SELECT COUNT(*) DIV 3 AS r FROM lang) v
GROUP BY id MOD v.r;

    For both of the above queries, if you have more languages, just change the divisor in the subquery from 3 to whatever your language count is.

    What you describe in your example is a relationship between ids (1, 5, 9), (2, 6, 10), (3, 7, 11) & (4, 8, 12) but with no representation of this relationship in your table structure.

    A better approach would be to change the structure of your table to include a unique identifier for the string to be translated and an identifier for the language. One way to do this would to treat your English text as the base language string with a structure like:

    CREATE TABLE `lang` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `base_lang_id` INT UNSIGNED DEFAULT NULL,
    `lang_code` CHAR(2) NOT NULL DEFAULT 'en',
    `text` TEXT NOT NULL,
    FOREIGN KEY (`base_lang_id`) REFERENCES `lang` (`id`)
);
    id base_lang_id lang_code text
    1 NULL en English text1
    2 NULL en English text2
    3 NULL en English text3
    4 NULL en English text4
    5 1 fr French text1
    6 2 fr French text2
    7 3 fr French text3
    8 4 fr French text4
    9 1 ar Arabic text1
    10 2 ar Arabic text2
    11 3 ar Arabic text3
    12 4 ar Arabic text4

    With the above example, you could make the first four rows reference themselves and add a unique key on (base_lang_id, lang_code).

    Another approach is to have a unique text string to identify each string to be translated:

    CREATE TABLE `language_strings` (
    `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `unique_text_id` VARCHAR(255) NOT NULL,
    UNIQUE (`unique_text_id`)
);
    id unique_text_id
    1 UNIQUE_STRING_1
    2 UNIQUE_STRING_2
    3 UNIQUE_STRING_3
    4 UNIQUE_STRING_4 
    CREATE TABLE `translations` (
    `language_string_id` INT UNSIGNED NOT NULL,
    `lang_code` CHAR(2) NOT NULL DEFAULT 'en',
    `text` TEXT NOT NULL,
    PRIMARY KEY (`language_string_id`, `lang_code`),
    FOREIGN KEY (`language_string_id`) REFERENCES `language_strings` (`id`);
    language_string_id lang_code text
    1 en English text1
    2 en English text2
    3 en English text3
    4 en English text4
    1 fr French text1
    2 fr French text2
    3 fr French text3
    4 fr French text4
    1 ar Arabic text1
    2 ar Arabic text2
    3 ar Arabic text3
    4 ar Arabic text4
    Login or Signup to reply.
  4. 0

    If you want to go your way, since it is a one off action, you go best by creating a new table with all the language fields you need, and just do a query one at a time per language, since your language field have unique names.

    INSERT INTO newTable (id, englishText)
SELECT mod(id-1, <nr of languages>)+1)+1, Text
FROM oldTable
ON DUPLICATE KEY UPDATE englishText=VALUE(englishText)

    And run this for every language with the appropriate field name.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search