1

What's a good way to concat a column based on a match with another column in MySQL? And possible add a limit?

Say my table looks like this:

City | State | Stores
----------------------
Miami  FL      Walmart
Miami  FL      Sams
Miami  FL      Target
Tampa  FL      Sears
Tampa  FL      Walgreens

And I want to get something like this:

City | State | Stores
----------------------
Miami  FL      Walmart, Sams, Target
Tampa  FL      Sears, Walgreens

Also, is it possible to set a limit on how many are concated? Say I want a limit of 2, in the above sample it would exclude Target after getting 2 items. Is this possible?

Federico Razzoli
  • 4,901
  • 1
  • 19
  • 21
VDH
  • 149
  • 11
  • 1
    If limiting to two records per group, how do you decide *which* two to select? – eggyal May 22 '13 at 15:13
  • @eggyal - thanks! I didn't know that, and didn't really know you could link to an answer until you pointed this out. I'll update the comment. – Ed Gibbs May 22 '13 at 15:22
  • The GROUP_CONCAT from @FreshPrinceOfSO will string them together for you. To limit the number of stores, see [this StackOverflow answer to a similar question](http://stackoverflow.com/a/10986929/2091410). – Ed Gibbs May 22 '13 at 15:24

2 Answers2

4

You can use both GROUP_CONCAT and SUBSTRING_INDEX to concatenate and then limit the number of values to return:

select city,
  state,
  substring_index(group_concat(stores SEPARATOR ', '), ', ', 2) stores
from yt                                                   -- ^ number of values to limit
group by city, state;

See SQL Fiddle with Demo

Taryn
  • 242,637
  • 56
  • 362
  • 405
0

You can use GROUP_CONCAT.

SELECT City, State, GROUP_CONCAT(Stores SEPARATOR ', ') Stores
FROM table
GROUP BY City, State

Result

|  CITY | STATE |                STORES |
-----------------------------------------
| Miami |    FL | Walmart, Sams, Target |
| Tampa |    FL |     Sears, Walgreens  |

See a demo

Kermit
  • 33,827
  • 13
  • 85
  • 121