php - 用mysql将表的列划分为不同的行

Question

这是表格:TABLE1

  ID | id_activity | id_elem | text 
---------------------------------------
  1  |      1      |    11   | text1  |
  2  |      1      |    12   | text2  |
  3  |      1      |    13   | text3  |
  4  |      2      |    11   | text4  |
  5  |      2      |    12   | text5  | 
  6  |      2      |    13   | text6  |
  7  |      3      |    11   | text7  | 
  8  |      3      |    12   | text8  |
  9  |      3      |    13   | text9  |
  10 |      4      |    11   | text10 | 
  11 |      4      |    12   | text11 |
  12 |      4      |    13   | text12 |
  13 |      5      |    11   | text13 |
  14 |      5      |    12   | text14 | 
  15 |      5      |    13   | text15 |
  16 |      6      |    11   | text16 |
  17 |      6      |    12   | text17 | 
  18 |      6      |    13   | text18 | 

我需要做出这样的结果:

  ID | text_elem_11 | text_elem_12 | text_elem_13 
---------------------------------------------------
  1  |    text1     |    text2     |    text3     |
  2  |    text4     |    text5     |    text6     |
  3  |    text7     |    text8     |    text9     |
  4  |    text10    |    text11    |    text12    |
  5  |    text13    |    text14    |    text15    | 
  6  |    text16    |    text17    |    text18    |

正确的做法是什么？通过以下查询，我只能得到一个包含第一列和第二列的表

SELECT table1.ID,
       table1.id_elem,
       elem_11.text AS text_elem_11

FROM table1

INNER JOIN table1 AS elem_11 ON
table1.id_activity = 1 AND 
table1.id_elem = 11 AND 
elem_11.id_elem = 11

这是结果

  id_activity | id_elem  | text_elem_11 | 
-----------------------------------------
       1      |    11    |    text1     |
       1      |    11    |    text4     |
       1      |    11    |    text7     |

我不知道如何添加其他 2 列，如果通过单个查询来完成是否是个好主意...那么，有什么想法吗？

Answer 1

SELECT  id_activity,
        MAX(CASE WHEN id_elem = 11 THEN text END) text_elem_11,
        MAX(CASE WHEN id_elem = 12 THEN text END) text_elem_12,
        MAX(CASE WHEN id_elem = 13 THEN text END) text_elem_13
FROM    TableName
GROUP   BY id_activity
ORDER   BY id_activity

• SQLFiddle Demo

输出

╔═════════════╦══════════════╦══════════════╦══════════════╗
║ ID_ACTIVITY ║ TEXT_ELEM_11 ║ TEXT_ELEM_12 ║ TEXT_ELEM_13 ║
╠═════════════╬══════════════╬══════════════╬══════════════╣
║           1 ║ text1        ║ text2        ║ text3        ║
║           2 ║ text4        ║ text5        ║ text6        ║
║           3 ║ text7        ║ text8        ║ text9        ║
║           4 ║ text10       ║ text11       ║ text12       ║
║           5 ║ text13       ║ text14       ║ text15       ║
║           6 ║ text16       ║ text17       ║ text18       ║
╚═════════════╩══════════════╩══════════════╩══════════════╝

如果你有未知数量的 id_elem，动态 sql 更受欢迎，

SET @sql = NULL;

SELECT  GROUP_CONCAT(DISTINCT
        CONCAT('MAX(CASE WHEN id_elem = ',
               id_elem,
               ' THEN text ELSE NULL END) AS ',
               CONCAT('`text_elem_' , id_elem, '`')
               )) INTO @sql
FROM TableName
ORDER BY id_elem;



SET @sql = CONCAT('SELECT   id_activity, ', @sql, ' 
                    FROM    TableName
                    GROUP   BY id_activity
                    ORDER   BY id_activity');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

• SQLFiddle Demo