MySQL How to display a row value as a column name using concat and group_concat.

Question

Table 1:

id   | typeid | available|
0    | 1      | 12       |
0    | 2      | 44       |

Table 2:

typeid   | typename   |
1        | CL          |
2        | ML          |

I have a query using concatand group_concat:

select id,concat(group_concat(typename,available)) as types from table1
join table2 on table2.typeid=table1.typeid

I got the result as:

id | types   | 
0  | CL12,ML44 |

But I want to show it as follows:

id   | CL   | ML    |
0    | 12   | 44    |

Is it possible to split the result group_concatinto column headings ?

I want to dynamically retrieve data from table2. Some users may add data to table2. So hard coding of the name is impossible.

Answer 1

You must use table rotation. There is no PIVOT command in MySQL, so you can use this query -

SELECT
  t1.id,
  MAX(IF(t2.typename = 'CL', t1.available, NULL)) AS CL,
  MAX(IF(t2.typename = 'ML', t1.available, NULL)) AS ML
FROM table1 t1
  JOIN table2 t2
    ON t1.typeid = t2.typeid
GROUP BY
  t1.id;

MySQL ( ).

GROUP_CONCAT MAX, available.

Answer 2

:

SELECT a.id, MAX(IF(b.typename = 'CL', a.available, 0)) CL, 
       MAX(IF(b.typename = 'ML', a.available, 0)) ML
FROM table1 a
INNER JOIN table2 b ON a.typeid=b.typeid
GROUP BY a.id;

, someualr else, , ii.

Answer 3

This can only be done if you know all the types of names in advance - otherwise you will need to find another way. In databases that support the return of result sets from stored procedures, this can be done using the stored procedure. But mysql does not support this.

If you know all types of names, here is how you build a query:

SELECT
    id,
    SUM(IF(typename = 'CL', available, 0)) AS `CL`,
    SUM(IF(typename = 'ML', available, 0)) AS `ML`
FROM table1 join table2 on table1.typeid = table2.typeid
GROUP BY id