mysql combine_MySQL Combine row values with comma and more

问题

I have been banging my head over this for a while. Mainly it's not about getting a solution BUT how it works. It would be great if you can give explanation on how the solution is implemented.

This is just sample data, I am working with 5 tables with inline SELECT statements. Thank you SQL Gurus.

Sample Tables:

CREATE TABLE source

(

product varchar(20),

amount decimal

)

CREATE TABLE cat

(

category varchar(20),

product varchar(20)

)

Insert Sample Data:

INSERT INTO source (product, amount) VALUES

('product1', 1.00),

('product2', 2.00),

('product3', 1.50),

('product4', 5.25)

INSERT INTO cat (category, product) VALUES

('Shirts', 'product1'),

('Green Shirt', 'product1'),

('Gadget', 'product1'),

('Food', 'product2'),

('Games', 'product4')

My Trial Query:

DECLARE @categories varchar(200)

SET @categories = NULL

SELECT

@categories = COALESCE(@categories + ',','') + cat.category

FROM cat

SELECT

DISTINCT source.product,

@categories

FROM cat

JOIN source on source.product = cat.product

OUTPUT

PRODUCT COLUMN_1

product1 Shirts,Green Shirt,Gadget,Food,Games

product2 Shirts,Green Shirt,Gadget,Food,Games

product4 Shirts,Green Shirt,Gadget,Food,Games

DESIRED OUTPUT:

PRODUCT COLUMN_1

product1 Shirts, Green Shirt, Gadget

product2 Gadget, Food

product4 Games

回答1:

I think this is what you're looking for using GROUP_CONCAT:

SELECT

source.product,

GROUP_CONCAT(cat.category) cats

FROM cat

JOIN source on source.product = cat.product

GROUP BY source.product

SQL Fiddle Demo

If you're trying to get a ', ' in between each category, use SEPARATOR with GROUP_CONCAT. For example:

GROUP_CONCAT(cat.category separator ', ')

来源：https://stackoverflow.com/questions/16720129/mysql-combine-row-values-with-comma-and-more