MySQL Transponse and redesign the table



  • The table is as follows:

    tschoice_onechoice_twocorrect_onecorrect_two
    111okokokcrit
    222okokokok

    A request for SQL is required and is based on the following:

    tscategorychoicecorrect
    111oneokok
    111twookcrit
    222oneokok
    222twookok

    Description of developments

    Columns of choice and correct (was part of the headings), and one and two (second part of the headings) shall be assigned to the new column category. At the intersection, their respective values remain.

    In the working table of some 20 categories one, two, three, etc. (the names are known in advance), for each of which there is a choice - correct (factic and correct marking of categories, respectively) of about 40 columns. We have to do this as a vertical decoration when there are only four above-mentioned columns, but the number of lines will increase.



  • With Akina's clue, I got a decision. We use UNION ALL, or in my case UNION (UNION ALL + DISTINCT)

    CREATE TABLE mytable (
      ts INT,
      choice_one varchar(50),
      choice_two varchar(50),
      etalon_one varchar(50),
      etalon_two varchar(50)
    );
    

    INSERT INTO mytable (ts, choice_one, choice_two, etalon_one, etalon_two) VALUES
    ('111', 'ok', 'ok', 'ok', 'crit'),
    ('222', 'ok', 'ok', 'ok', 'ok');

    SELECT ts, choice_one AS choice, 'one' AS category
    FROM mytable
    UNION
    SELECT ts, choice_two AS choice, 'two' AS category
    FROM mytable
    UNION
    SELECT ts, etalon_one AS correct, 'one' AS category
    FROM mytable
    UNION
    SELECT ts, etalon_two AS correct, 'two' AS category
    FROM mytable

    Depending on the case, it can put choise and correct in one column. In my case, it's doubled.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2