INSERT
INTO
cities
VALUES
(
'San Francisco'
,
'CA'
)
;
INSERT
INTO
cities
VALUES
(
'San Diego'
,
'CA'
)
;
INSERT
INTO
cities
VALUES
(
'Los Angeles'
,
'CA'
)
;
INSERT
INTO
cities
VALUES
(
'Austin'
,
'TX'
)
;
INSERT
INTO
cities
VALUES
(
'Houston'
,
'TX'
)
;
The query groups cities by state and returns a list of cities in each state:
SELECT state, GROUP_CONCAT(city)
FROM cities
GROUP BY state;
The result:
Oracle supports LISTAGG function that can concatenate the values within a group. By default, the delimiter is empty string, so we can to specify comma explicitly:
SELECT state, LISTAGG(city, ',') WITHIN GROUP (ORDER BY NULL)
FROM cities
GROUP BY state;
Result:
SQL
Server does not provide an aggregate function to concatenate values within a group. Using a correlated subquery, FOR
XML
clause and STUFF function we can achieve MySQL GROUP_CONCAT functionality:
SELECT state,
STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'')
FROM cities c
GROUP BY state
Result:
Correlated subquery gets a list of cities for each state (multiple rows if there are multiple cities in state)
FOR
XML
PATH converts
multiple
rows into a
single
row
STUFF function is used to remove the first comma
PostgreSQL provides STRING_AGG function to concatenate values within a group. You must specify a delimiter:
SELECT state, STRING_AGG(city, ',')
FROM cities
GROUP BY state;
Result:
