CODEWITHSUNDEEP
mysql
epynic
epynic

Reputation: 1164

MySQL GROUP_CONCAT with separator

Suppose I have a table 'pincodes' as

-------------------------------------
| id  | name  |  parent_id  | value |
-------------------------------------
|  1  | State |     0       |   0   |
|  2  | City1 |     1       | 20220 |
|  3  | City2 |     1       | 20221 |

The result i need is 

--------------------------
| 1 | State       |      |
| 2 | State>City1 | 20220|
| 3 | State>City2 | 20221|
--------------------------

What i tried out

SELECT id,
   GROUP_CONCAT(name ORDER BY parent_id SEPARATOR ' > ') AS name
FROM pincode
GROUP BY id ORDER BY name;

And the result for the above query is

--------------------
| 1 | State |      |
| 2 | City1 | 20220|
| 3 | City2 | 20221|
--------------------

what I need is the parent state should be appended to each city.

Note: can anyone suggest a better question title.

Upvotes: 0

Views: 4958

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

You can use the following query:

SELECT p1.id, CONCAT(COALESCE(CONCAT(p2.name, '>'), ''),  p1.name), p1.value
FROM pincodes AS p1
LEFT JOIN pincodes AS p2 ON p1.parent_id = p2.id
ORDER BY p1.id

You can get the parent record using a LEFT JOIN. Using CONCAT function you can concatenate the name of the parent with the name of the child.

Demo here

EDIT:

The above query works only for 2-level hierarchies. For additional levels you have to use additional LEFT JOIN operations. For a generic solution one has to use recursive CTEs that are not available in MySQL.

This is how the above query can be extended to handle 3-level hierarchies:

SELECT p1.id, 
       CONCAT(COALESCE(CONCAT(p3.name, '>'), ''),
              COALESCE(CONCAT(p2.name, '>'), ''),  
              p1.name), 
       p1.value
FROM pincodes AS p1
LEFT JOIN pincodes AS p2 ON p1.parent_id = p2.id
LEFT JOIN pincodes AS p3 ON p2.parent_id = p3.id
ORDER BY p1.id

Demo here

Upvotes: 2

Related Questions