将表列透视为行，如果一个表中不存在值，则使用另一个表中的值

Question

我想从没有理想布局的表中创建一个报告。我可以在excel中创建数据透视表，但我想为基于web的仪表板在线创建报告。我已经查看了用于透视表的http://datacharmer.org/downloads/pivot_tables_mysql_5.pdf和用于确定值是否存在的Select from table if record found in another table，但我无法获得所需的结果。我可以在mysql中创建一个带有临时表的数据透视表，但是报表软件不允许数据操作。

一些答案(qid 742)在survey_survey_144477表中，但其他答案在答案表中。因此，如果qid不存在于survey_answers中，我尝试使用survey_survey_144477中的值。

这只适用于一个特定的报告。调查结果在表survey_survey_144477中，只有几个问题，但其他调查可能有20或30个问题。所以，我喜欢让这个动态，这样我就不需要更改每个报告的所有列标题了。我还希望解析列，因为survey_survey_144477中的部分列有qid，但没有成功。我还尝试确定如何选择所有列，但忽略一些列，例如token、last page等，因为我认为如果我将其设置为动态的，但又没有运气的话，这将是有帮助的。

如果你能提供任何帮助，我将不胜感激。

期望的输出将是：

| 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                |

我试过了：

## 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 |
+-----+------+-----------+-----------+------------------+----------+----------+

更新：

我创建了一个表来将值从一个表映射到另一个表。自动化这将是一个挑战，我将在后面提到。

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_<id> 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');

这导致了

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'

尝试获取问题和答案。

 -- 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'

--返回多行

  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;

如果类型是'B'，'M'，'L‘，这是有效的。

   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'

--错误:子查询返回多行。

  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;

如果类型是'B'，'M'，'L‘，则从表survey_answers中获取答案，如果它们是另一种类型，则从表survey_answers_lookup中获取答案。如果答案不在表survey_answers中，则qid不在该表中。表survey_answers_lookup对qid的742和748有三个答案，查询不知道要获取哪个值。我想我需要添加另一个值来加入它。谢谢

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'

它可能会很混乱，但也许我可以添加一个计数器，然后将该计数器与表survey_answers_lookup中的id进行比较？

select answer
             from survey_answers_lookup
                  where qid not in (select qid from survey_answers)
                  and id = @counter

我想我让它起作用了。

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'

现在，我需要创建一个触发器，用于在创建新调查并回答问题时更新此表。

(摘自评论)

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;

Answer 1

每当我需要旋转的时候，我都会考虑用SQL来做。但我很快就把这个想法打发掉了，因为太痛苦了，无法处理。相反，我用PHP编写代码来透视存储的数据，这对于数据库来说是一种更“自然”的方式--每行一行，而不是每一行一列。

这应该可以解决每个调查一张表的问题。如此快速地创建表通常是糟糕的设计。

也就是说，我决定看看在SQL中自动化这个过程需要做些什么。仍然是一个。

Answer 2

试着总结这个问题：

给定这3个表，

问题：

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_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'

答案：

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 |
+-----+------+-----------+-----------+------------------+----------+----------+

期望的输出将是：

| 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                |

(观点:这在宽度上很笨拙。)

让我们分三步来做：

步骤1:获取数据：

SELECT  qid, q.question, a.answer
    FROM survey_questions AS q
    JOIN survey_answers_lookup AS al  USING(sid, qid)
    JOIN survey_answers AS a  USING(sid, qid)
    WHERE sid = 144477
      AND al.answer = a.code
    ORDER BY qid;

看看这是否获得了所需的输出，但尚未进行透视。当您对此感到满意时，让我们继续执行步骤2。

步骤2：CASE WHEN。我不明白你在说什么--我从表中看不出来。

第3步:轴心

将其作为一个子查询提供给由生成的代码

http://mysql.rjweb.org/doc.php/pivot