Group all fields on one line

Question

How to make a request to group all fields in one line?

Table:

id | type --------- 1 | 1 1 | 1 2 | 2 3 | 3 3 | 3 

request:

 select concat('id(', count(type), ')') from T group by id; 

exit:

 id(2) id(1) id(2) 

I want to get a line like: 'id(1)' = 2, 'id(2)' = 1, 'id(3)' = 2

Answer 1

If you do this in two steps. Make your entries id(n) = x and then GROUP_CONCAT ().

 SELECT GROUP_CONCAT(id_count SEPARATOR ', ') FROM (SELECT CONCAT('id(', id, ') = ', count(type)) id_count FROM T GROUP BY id) data 

But note that this is often a sign of SQL Anti-Pattern.

It is generally recommended not to compress multiple values ​​into a single value. And it is usually recommended to maintain separation of view and data.