MySQL - How to display row value as 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 concat and 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 display it like this:

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

Is there any way to split the group_concat result to columns heads?

I want dynamically fetch data from table2. Some user can add data to table2. So hard-coding typename is not possible.

Devart · Accepted Answer · 2013-03-26 06:25:07Z

4

You should use table pivoting. 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 pivot tables (transform rows to columns).

Use GROUP_CONCAT function instead of MAX, if multiple available values are possible.

edited Mar 26, 2013 at 6:25

answered Dec 21, 2012 at 7:00

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Jos Over a year ago

But now a challenge: How about if you don't know the typename values? Is it at all possible then?

Lee · Accepted Answer · 2012-12-21 07:02:09Z

0

It can be done only if you know all the typenames in advance -- otherwise, you'll need to find another way. In databases that support returning result sets from stored procedures, it could be done with a stored proc. But mysql doesn't support that.

If you know all the typenames, here's how you build the 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

answered Dec 21, 2012 at 7:02

Lee

13.5k1 gold badge34 silver badges46 bronze badges

Comments

Saharsh Shah · Accepted Answer · 2012-12-21 07:40:49Z

0

Try this:

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;

Use SUM function if you want to sum of the data from available column for particualr type else use the same query as ii is.

edited Dec 21, 2012 at 7:40

answered Dec 21, 2012 at 7:01

Saharsh Shah

29.1k9 gold badges53 silver badges85 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related