Divide a table's column as different rows with mysql

Question

here's the table: 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 |

I need to make a result like this:

  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    |

What is the right way to do this? With the follow query I can only get a table with the first and the second column

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

This is the result

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

i don't know how to add the others 2 columns and if is a good idea to do it with a single query... so, any idea?

Consider handling issues of data display at the application level. It's more flexible that way. — Strawberry
– Strawberry, Commented May 8, 2013 at 17:18

John Woo · Accepted Answer · 2013-05-08 17:18:00Z

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

OUTPUT

╔═════════════╦══════════════╦══════════════╦══════════════╗
║ 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       ║
╚═════════════╩══════════════╩══════════════╩══════════════╝

If you have unknown number of id_elem, a dynamic sql is much more preferred,

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

Community · Accepted Answer · 2017-05-23 12:03:26Z

What you are looking for is called "select select" or select subqueries.

( I have removed my initial thought that MySQL does support such thing as select (select column_names from other_table) from table )

Unfortunately although such support exists it may not be exactly what you are looking for, because the subquery has to return single literal, instead of list of columns names.

I stumbled across another question on the topic here: Select MYSQL rows but rows into columns and column into rows - check it out if such complicated view gets the job done for you. Otherwise I recommend you select the rows in your code and then iterate them, creating a new set with rows converted to columns - if you want I can post you some code ideas, because I remember I have had that problem in the past

Edit: I realised I have jumped the solution a bit. Even select select wouldn't help you! Will have to think a bit deeper. Interesting question!

Collectives™ on Stack Overflow

Divide a table's column as different rows with mysql

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related