title: Pivot table columns to rows and use a value in one table if it does not exists in another
body: <p>I'd like to create a report from tables that don't have an ideal layout. I can create pivot tables in excel but I want to create the reports online for a web based dashboard.  I've looked at
  <a href="http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf" rel="nofollow noreferrer">http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf</a> for pivot tables and <a href="https://stackoverflow.com/questions/18310838/select-from-table-if-record-found-in-another-table">Select from table if record found in another table</a> for determining if a value exists but I cannot get the desired results. I was able to create a pivot table with a temp table in mysql but the reporting software would not allow data manipulation.</p>
  
  <p>Some answers (qid 742) are in the survey_survey_144477 table but others are in the answers table. So I tried to use the value from survey_survey_144477 if the qid did nto exist in survey_answers.</p>
  
  <p>This is only for one specific report. The survey results are in table survey_survey_144477 and only have a few questions but other surveys may have 20 or 30 questions.  So, I'd liek to make this dynmaic so I won't have to change all of the column heading for every report.  I also looked for parsing the column because part of the columns in survey_survey_144477 have the qid but no luck.  I also treied to determine how I could select all of the colums but disregard some such as token, last page, etc. because I thought it would be helpful if I made it dynamic but also no luck.</p>
  
  <p>I'd appreciate any help you can offer.</p>
  
  <p>The desired output would be:</p>
  
  <pre><code>| How would you rate ... | Do you think our product ... | One a scale of 1-10 ... | Will you recommend |
  +-----------------+------+------------------------------+-------------------------+--------------------+
  | Excellent              | Yes                          | 7                       | Yes                |
  | Very good              | No                           | 8                       | Yes                |
  </code></pre>
  
  <p>I have tried:</p>
  
  <pre><code>## Create pivot table
  CREATE TEMPORARY TABLE temp
  SELECT id,'144477X148X740' AS lid, 740 AS qid, 144477X148X740 AS value FROM survey_survey_144477
  UNION ALL
  SELECT id, '144477X148X741' AS lid, 741 AS qid, 144477X148X741 AS value FROM survey_survey_144477
  UNION ALL
  SELECT id, '144477X148X742SQ001' AS lid, 742 AS qid, 144477X148X742SQ001 AS value FROM survey_survey_144477
  UNION ALL
  SELECT id, '144477X148X745' AS lid, 745 AS qid, 144477X148X745 AS value FROM survey_survey_144477;
  
  
  ## Get the answers.  Table survey_answers has answers from dropdowns and selection boxes.
  ## Other answers are in the temp table.
  select T.id, SQ.question, SA.answer
     from survey_questions SQ
     join survey_answers SA on SA.qid = SQ.qid
     join temp T on T.qid = SQ.qid
     where SQ.sid = 144477
     --   and exists (select SA.answer as answer
     --                      from survey_answers SA
     --                     join survey_questions SQ on SA.qid = SQ.qid
     --                     join temp T on T.qid = SQ.qid)
  
  CREATE TEMPORARY TABLE temp2
  select id, qid, value
     from temp
     where not exists (select qid from survey_answers
                             where temp.qid = survey_answers.qid);
  
  
  
      describe survey_survey_144477;
      +---------------------+----------------+------+-----+---------+----------------+
      | Field               | Type           | Null | Key | Default | Extra          |
      +---------------------+----------------+------+-----+---------+----------------+
      | id                  | int(11)        | NO   | PRI | NULL    | auto_increment |
      | token               | varchar(35)    | YES  |     | NULL    |                |
      | submitdate          | datetime       | YES  |     | NULL    |                |
      | lastpage            | int(11)        | YES  |     | NULL    |                |
      | startlanguage       | varchar(20)    | NO   |     | NULL    |                |
      | ipaddr              | text           | YES  |     | NULL    |                |
      | refurl              | text           | YES  |     | NULL    |                |
      | 144477X148X740      | varchar(5)     | YES  |     | NULL    |                |
      | 144477X148X741      | varchar(5)     | YES  |     | NULL    |                |
      | 144477X148X742SQ001 | decimal(30,10) | YES  |     | NULL    |                |
      | 144477X148X745      | varchar(5)     | YES  |     | NULL    |                |
      | 144477X148X748      | text           | YES  |     | NULL    |                |
      +---------------------+----------------+------+-----+---------+----------------+
  
      describe survey_questions ;
      +----------------+--------------+------+-----+---------+----------------+
      | Field          | Type         | Null | Key | Default | Extra          |
      +----------------+--------------+------+-----+---------+----------------+
      | qid            | int(11)      | NO   | PRI | NULL    | auto_increment |
      | parent_qid     | int(11)      | NO   | MUL | 0       |                |
      | sid            | int(11)      | NO   | MUL | 0       |                |
      | gid            | int(11)      | NO   | MUL | 0       |                |
      | type           | varchar(1)   | NO   | MUL | T       |                |
      | title          | varchar(20)  | NO   |     |         |                |
      | question       | mediumtext   | NO   |     | NULL    |                |
      | preg           | mediumtext   | YES  |     | NULL    |                |
      | help           | mediumtext   | YES  |     | NULL    |                |
      | other          | varchar(1)   | NO   |     | N       |                |
      | mandatory      | varchar(1)   | YES  |     | NULL    |                |
      | question_order | int(11)      | NO   |     | NULL    |                |
      | language       | varchar(20)  | NO   | PRI | en      |                |
      | scale_id       | int(11)      | NO   |     | 0       |                |
      | same_default   | int(11)      | NO   |     | 0       |                |
      | relevance      | mediumtext   | YES  |     | NULL    |                |
      | modulename     | varchar(255) | YES  |     | NULL    |                |
      +----------------+--------------+------+-----+---------+----------------+
  
      describe survey_answers ;
      +------------------+-------------+------+-----+---------+-------+
      | Field            | Type        | Null | Key | Default | Extra |
      +------------------+-------------+------+-----+---------+-------+
      | qid              | int(11)     | NO   | PRI | 0       |       |
      | code             | varchar(5)  | NO   | PRI |         |       |
      | answer           | mediumtext  | NO   |     | NULL    |       |
      | sortorder        | int(11)     | NO   | MUL | NULL    |       |
      | assessment_value | int(11)     | NO   |     | 0       |       |
      | language         | varchar(20) | NO   | PRI | en      |       |
      | scale_id         | int(11)     | NO   | PRI | 0       |       |
      +------------------+-------------+------+-----+---------+-------+
  
      select id, 144477X148X740, 144477X148X741, 144477X148X742SQ001, 144477X148X745, 144477X148X748 from survey_survey_144477 limit 10;
      +----+----------------+----------------+---------------------+----------------+----------------+
      | id | 144477X148X740 | 144477X148X741 | 144477X148X742SQ001 | 144477X148X745 | 144477X148X748 |
      +----+----------------+----------------+---------------------+----------------+----------------+
      |  1 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  2 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  3 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  4 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  5 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  6 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  7 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  8 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      |  9 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      | 10 | 1              | A2             |        5.0000000000 | A2             | name@email.com |
      +----+----------------+----------------+---------------------+----------------+----------------+
  
  select qid, sid, question from survey_questions where sid = 144477;
  +-----+--------+-------------------------------------------------------------------------------------------+
  | qid | sid    | question                                                                                  |
  +-----+--------+-------------------------------------------------------------------------------------------+
  | 748 | 144477 | Please enter your email address or phone number if you would like someone to contact you. |
  | 740 | 144477 | How would you rate our product's quality?                                                 |
  | 741 | 144477 | Do you think our product helps your business?                                             |
  | 742 | 144477 | One a scale of 1-10, how would you rate the value of our product?                         |
  | 743 | 144477 |                                                                                           |
  | 745 | 144477 | Will you recommend our product?                                                           |
  +-----+--------+-------------------------------------------------------------------------------------------+
  
  select * from survey_answers where qid between 740 and 745;
  +-----+------+-----------+-----------+------------------+----------+----------+
  | qid | code | answer    | sortorder | assessment_value | language | scale_id |
  +-----+------+-----------+-----------+------------------+----------+----------+
  | 740 | 1    | Excellent |         1 |                1 | en       |        0 |
  | 740 | 2    | Very good |         2 |                1 | en       |        0 |
  | 740 | 3    | Good      |         3 |                1 | en       |        0 |
  | 740 | 4    | Fair      |         4 |                1 | en       |        0 |
  | 740 | 5    | Poor      |         5 |                1 | en       |        0 |
  | 741 | A1   | Yes       |         1 |                0 | en       |        0 |
  | 741 | A2   | No        |         2 |                0 | en       |        0 |
  | 745 | A1   | Yes       |         1 |                0 | en       |        0 |
  | 745 | A2   | No        |         2 |                0 | en       |        0 |
  +-----+------+-----------+-----------+------------------+----------+----------+
  </code></pre>
  
  <p>Update:</p>
  
  <p>I created a table to map values from one table to another. Automating this will be a challenge that I will mention later.</p>
  
  <pre><code>CREATE TABLE `survey_answers_lookup` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `sid` int(11) NOT NULL DEFAULT '0',
    `qid` int(11) NOT NULL DEFAULT '0',
    `survey_table_row_id` int(11) NOT NULL DEFAULT '0' COMMENT 'id that is in the survey_&lt;id&gt; table',
    `answer` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`)
  );
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,740,1,1);
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,741,1,'A1');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,742,1,'5.0000000000');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,745,1,'A1');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,748,1,'1@email.com');
  
  INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
  VALUES (144477,740,2,'2');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,741,2,'A2');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,742,2,'8.0000000000');
  
  INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
  VALUES (144477,745,2,'A2');
  
  INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
  VALUES (144477,748,2,'2@email.com');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,740,3,'5');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,741,3,'A2');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,742,3,'10.0000000000');
  
  INSERT INTO survey_answers_lookup (sid,qid,survey_table_row_id,answer)
  VALUES (144477,745,3,'A2');
  
  INSERT INTO survey_answers_lookup(sid,qid,survey_table_row_id,answer)
  VALUES (144477,748,3,'3@email.com');
  </code></pre>
  
  <p>Which results in</p>
  
  <pre><code>select * from survey_answers_lookup;
  
  # id, sid, qid, survey_table_row_id, answer
  '1', '144477', '740', '1', '1'
  '2', '144477', '741', '1', 'A1'
  '3', '144477', '742', '1', '5.0000000000'
  '4', '144477', '745', '1', 'A1'
  '5', '144477', '748', '1', '1@email.com'
  '6', '144477', '740', '2', '2'
  '7', '144477', '741', '2', 'A2'
  '8', '144477', '742', '2', '8.0000000000'
  '9', '144477', '745', '2', 'A2'
  '10', '144477', '748', '2', '2@email.com'
  '11', '144477', '740', '3', '5'
  '12', '144477', '741', '3', 'A2'
  '13', '144477', '742', '3', '10.0000000000'
  '14', '144477', '745', '3', 'A2'
  '15', '144477', '748', '3', '3@email.com'
  </code></pre>
  
  <p>Attempts to get the questions and answers.</p>
  
  <pre><code> -- Gives all of the rows from the answers_table even if that is not in the 
   -- survey_answers_lookup table because of the left join.  740 should only be printed three times.
      select
          --SQ.qid,
          SA.code,
          SQ.question,
          SQ.type,
          -- Types can be: ! * 1 E F K L M N Q R S T X Y
          if (SQ.type IN ('B','M','L'), SA.answer, LU2.answer) as 'answer'
          from survey_questions SQ
             left join survey_answers SA on SA.qid = SQ.qid
                -- and SQ.type IN ('B','M','L')
             left join survey_answers_lookup LU on LU.qid = SQ.qid 
                 and LU.answer = SA.code
                -- and SQ.type IN ('B','M','L')
             left join survey_answers_lookup LU2 on LU2.qid = SQ.qid 
                  and SQ.title = 'Q7'
        where SQ.sid = 144477;
  
  # --SQ.qid, code, question, type, answer
  '740', '1', 'How would you rate our product\'s quality?', 'L', 'Excellent'
  '740', '2', 'How would you rate our product\'s quality?', 'L', 'Very good'
  '740', '5', 'How would you rate our product\'s quality?', 'L', 'Poor'
  '740', '3', 'How would you rate our product\'s quality?', 'L', 'Good'
  '740', '4', 'How would you rate our product\'s quality?', 'L', 'Fair'
  '741', 'A1', 'Do you think our product helps your business?', 'L', 'Yes'
  '741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
  '741', 'A2', 'Do you think our product helps your business?', 'L', 'No'
  '742', NULL, 'One a scale of 1-10, how would you rate the value of our product?', 'K', NULL
  '745', 'A1', 'Will you recommend our product?', 'L', 'Yes'
  '745', 'A2', 'Will you recommend our product?', 'L', 'No'
  '745', 'A2', 'Will you recommend our product?', 'L', 'No'
  '748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '1@email.com'
  '748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '2@email.com'
  '748', NULL, 'Please enter your email address or phone number if you would like someone to contact you.', 'S', '3@email.com'
  </code></pre>
  
  <p>-- Returns more than one row  </p>
  
  <pre><code>  select
      SQ.qid,
      SQ.question,
      SQ.type,
      -- Types can be: ! * 1 E F K L M N Q R S T X Y
      if (SQ.type IN ('B','M','L'), 
          (select SA.answer
             from survey_answers SA 
                join survey_questions SQ2 on SQ2.qid = SA.qid
                join survey_answers_lookup LU on LU.qid = SQ2.qid 
                   and LU.answer = SA.code),
            (select LU2.answer 
                from survey_answers_lookup LU2 
                join survey_questions SQ3 on SQ3.qid = LU2.qid)         
       ) as answer
        from survey_questions SQ        
        where SQ.sid = 144477;
  </code></pre>
  
  <p>This works if the types are 'B','M','L'.</p>
  
  <pre><code>   select
      -- qid,
      -- SA.code,
      question,
      -- Types can be: ! * 1 E F K L M N Q R S T X Y
      if (type IN ('B','M','L'), 
          (select answer
             from survey_answers
                where survey_questions.qid = survey_answers.qid
                and survey_answers_lookup.qid = survey_questions.qid 
                and survey_answers_lookup.answer = survey_answers.code
           ),
           (0 -- select answer 
              --  from survey_answers_lookup 
               --  where survey_questions.qid = survey_answers_lookup.qid
            )         
       ) as answer
        from survey_questions 
        join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
        where survey_questions.sid = 144477;
  
  'Do you think our product helps your business?', 'Yes'
  'Do you think our product helps your business?', 'No'
  'Do you think our product helps your business?', 'No'
  'How would you rate our product\'s quality?', 'Excellent'
  'How would you rate our product\'s quality?', 'Very good'
  'How would you rate our product\'s quality?', 'Poor'
  'One a scale of 1-10, how would you rate the value of our product?', '0'
  'One a scale of 1-10, how would you rate the value of our product?', '0'
  'One a scale of 1-10, how would you rate the value of our product?', '0'
  'Please enter your email address or phone number if you would like someone to contact you.', '0'
  'Please enter your email address or phone number if you would like someone to contact you.', '0'
  'Please enter your email address or phone number if you would like someone to contact you.', '0'
  'Will you recommend our product?', 'Yes'
  'Will you recommend our product?', 'No'
  'Will you recommend our product?', 'No'
  </code></pre>
  
  <p>-- Error: Subquery returns more than one row.</p>
  
  <pre><code>  select
  -- qid,
  -- SA.code,
  question,
  -- Types can be: ! * 1 E F K L M N Q R S T X Y
  if (type IN ('B','M','L'), 
      (select answer
         from survey_answers
            where survey_questions.qid = survey_answers.qid
            and survey_answers_lookup.qid = survey_questions.qid 
            and survey_answers_lookup.answer = survey_answers.code
       ),
       (select answer 
           from survey_answers_lookup
            join survey_questions on survey_questions.qid = survey_answers_lookup.qid
                where not exists (select * -- if the value is not in survey_answers table (not exists)
                                   from survey_answers
                                      where survey_answers.qid = survey_answers_lookup.qid
                                         LIMIT 1
                                )
        )         
   ) as answer
    from survey_questions 
    join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
    where survey_questions.sid = 144477;
  
  -- ----------------
  -- ----------------
  
        select
        -- survey_answers_lookup.survey_table_row_id,
      -- qid,
      -- SA.code,
      question,
      -- Types can be: ! * 1 E F K L M N Q R S T X Y
      if (type IN ('B','M','L'), 
          (select answer
             from survey_answers
                where survey_questions.qid = survey_answers.qid
                and survey_answers_lookup.qid = survey_questions.qid 
                and survey_answers_lookup.answer = survey_answers.code
           ),
           (select answer 
               from survey_answers_lookup
                   where qid not in (select qid  
                                       from survey_answers
                                    )
            )         
       ) as answer
        from survey_questions 
        join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
        where survey_questions.sid = 144477;
  </code></pre>
  
  <p>The idea is to get the answers from table survey_answers if the types are 'B','M','L' and to get the answers from table survey_answers_lookup if they are another type.  The qid is not in table survey_answers if the answers is not in that table. Table survey_answers_lookup has three answers for qid's 742 and 748 and the query does not know which value to get.  I guess I need to add another value to join it. Thanks  </p>
  
  <pre><code>select answer, qid
               from survey_answers_lookup
                    where qid not in (select qid from survey_answers)
  
  # answer, qid
  '5.0000000000', '742'
  '1@email.com', '748'
  '8.0000000000', '742'
  '2@email.com', '748'
  '10.0000000000', '742'
  '3@email.com', '748'
  </code></pre>
  
  <p>It might be messy but maybe I can add a counter and then compare the counter to the id in table survey_answers_lookup?</p>
  
  <pre><code>select answer
               from survey_answers_lookup
                    where qid not in (select qid from survey_answers)
                    and id = @counter
  </code></pre>
  
  <p>I think I got it to work.</p>
  
  <pre><code>set @counter = 0;
  
    select
       @counter := @counter+1 AS newindex,
        survey_answers_lookup.id as id,
        survey_answers_lookup.survey_table_row_id,
        survey_answers_lookup.qid,
        question,
      -- Types can be: ! * 1 E F K L M N Q R S T X Y
      if (type IN ('B','M','L'), 
          (select answer
             from survey_answers
                where survey_questions.qid = survey_answers.qid
                and survey_answers_lookup.qid = survey_questions.qid 
                and survey_answers_lookup.answer = survey_answers.code
           ),
           (select answer
               from survey_answers_lookup
                    where qid not in (select qid from survey_answers)
                    and id = @counter
            )      
       ) as answer
        from survey_questions 
        join survey_answers_lookup on survey_answers_lookup.qid = survey_questions.qid
        where survey_questions.sid = 144477
        order by survey_answers_lookup.id;
  
  # newindex, id, survey_table_row_id, qid, question, answer
  '1', '1', '1', '740', 'How would you rate our product\'s quality?', 'Excellent'
  '2', '2', '1', '741', 'Do you think our product helps your business?', 'Yes'
  '3', '3', '1', '742', 'One a scale of 1-10, how would you rate the value of our product?', '5.0000000000'
  '4', '4', '1', '745', 'Will you recommend our product?', 'Yes'
  '5', '5', '1', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '1@email.com'
  '6', '6', '2', '740', 'How would you rate our product\'s quality?', 'Very good'
  '7', '7', '2', '741', 'Do you think our product helps your business?', 'No'
  '8', '8', '2', '742', 'One a scale of 1-10, how would you rate the value of our product?', '8.0000000000'
  '9', '9', '2', '745', 'Will you recommend our product?', 'No'
  '10', '10', '2', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '2@email.com'
  '11', '11', '3', '740', 'How would you rate our product\'s quality?', 'Poor'
  '12', '12', '3', '741', 'Do you think our product helps your business?', 'No'
  '13', '13', '3', '742', 'One a scale of 1-10, how would you rate the value of our product?', '10.0000000000'
  '14', '14', '3', '745', 'Will you recommend our product?', 'No'
  '15', '15', '3', '748', 'Please enter your email address or phone number if you would like someone to contact you.', '3@email.com'
  </code></pre>
  
  <p>Now I need to create a trigger that updates this table when a new survey is created and questions are answered.</p>
  
  <p>(from comments)</p>
  
  <pre><code>SELECT  *
      FROM  survey_survey_144477
      UNION  ALL 
  SELECT  id, 
          144477X148X741,
          741 AS qid,
          144477X148X741 AS value
       FROM  survey_survey_144477
      UNION  ALL 
  SELECT  id,
          144477X148X742SQ001,
          742 AS qid,
          144477X148X742SQ001 AS value
       FROM  survey_survey_144477
      UNION  ALL 
  SELECT  id,
          144477X148X745, 745 AS qid,
          144477X148X745 AS value
       FROM  survey_survey_144477;
  
  select  T.id, SQ.question, SQ.type, -- SA.answer,
          case SQ.type
              when 'L' then SA.answer -- list choice --
              when 'M' then -- multiple choice --
              when 'B' then -- array-10(B)
              when 'T' OR  'N'  then T.value -- text or  numeric
              when 'K' then T.value -- slider --
              else "Do nothing" end as 'answer'
      from  survey_questions SQ
      join  survey_answers SA  ON SA.qid = SQ.qid
      join  temp T  ON T.qid = SQ.qid  and  T.value = SA.code
      where  SQ.sid = 144477 --
        and  SQ.type = 'K'
      order by  T.id;
  </code></pre>