基于某些组列连续多列

I have a table with name city having city, province and country columns and I want to display cities in a single row for each province with some separator.

我有一个名为city的表,其中包含城市,省和国家/地区列,我想在每个省份的单行中显示一些城市,并带有一些分隔符。

City Province    Country

A    X           Z  
B    X           Z
C    X           Z
D    Y           Z
E    Y           Z  
F    Y           Z

I want following results

我想要以下结果

Province    City

X           A | B | C
Y           D | E | F

1 个解决方案

#1

select Province, group_concat(City SEPARATOR '|')
FROM `tablename`
group by Province

Refer: http://sqlfiddle.com/#!2/58afb1/3