Q:

Multiple columns in a row based on some group column

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 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

A:

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

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

