Query for group by values from multiple column

Question

I have a table like:

ID key familyname  street     pin
1   1    xxx          abc      11
2   1    xxx          acf      13
3   1    xxx1         acf      13
4   1    xxx1         bcf      12
5   2    yyy          abc      11
6   2    yyy          abc      11
7   2    yyy1         bcj      13
8   2    yyy1         bck      14
9   2    yyy2         btj      15
10  2    yyy2         byj      16

Now I want to group street and pin based on columnn "familyname" for each individual key. For example I would expect the output for the above table to be

ID key familyname  street     pin
1   1    xxx        abc,acf    11,13

3   1    xxx1       acf,bcf    13,12

5   2    yyy        abc,abc    11,11

7   2    yyy1       bcj,bck    13,14

9   2    yyy2       btj,byj    15,16

It would be of great help if I can get some suggesion regarding the design of the grouping query which could give me the result in a fashion as shown above

thanks a lot

John Woo · Accepted Answer · 2013-04-10 08:49:24Z

MySQL has built-in functionality to concatenate rows for every group called GROUP_CONCAT().

SELECT  MIN(ID) ID,
        `key`,
        familyName,
        GROUP_CONCAT(street) street,
        GROUP_CONCAT(pin) pin
FROM    TableName
GROUP   BY `key`, familyName

OUTPUT

╔════╦═════╦════════════╦═════════╦═══════╗
║ ID ║ KEY ║ FAMILYNAME ║ STREET  ║  PIN  ║
╠════╬═════╬════════════╬═════════╬═══════╣
║  1 ║   1 ║ xxx        ║ abc,acf ║ 11,13 ║
║  3 ║   1 ║ xxx1       ║ acf,bcf ║ 13,12 ║
║  5 ║   2 ║ yyy        ║ abc,abc ║ 11,11 ║
║  7 ║   2 ║ yyy1       ║ bcj,bck ║ 13,14 ║
║  9 ║   2 ║ yyy2       ║ btj,byj ║ 15,16 ║
╚════╩═════╩════════════╩═════════╩═══════╝

Collectives™ on Stack Overflow

Query for group by values from multiple column

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related