Group_Concat
Returns a string with concatenated values from a group
- Free Function
- SQL Server Compatibility: 2008, 2012, 2014, 2016, 2017
SELECT SQLHTTP.net.Group_Concat ( @String, @Delimiter, @Sort )
| Name | Type | Description |
|---|---|---|
| @String | nvarchar(MAX) |
String expression to concatenate into final result. |
| Delimiter | nvarchar(MAX) |
A string, usually a single character such as a comma, to be used a separator between strings. If no delimiter is needed, use an empty string. |
| Sort | nvarchar(MAX) | String expression that determines the sort order of the strings in the final result. See examples below. |
nvarchar(MAX)
The following table variable is used for the examples below:
|
1 2 3 4 5 6 7 8 9 10 11 |
DECLARE @Continents TABLE(Continent varchar(50), Countries tinyint) INSERT INTO @Continents(Continent, Countries) VALUES('Asia', 47) INSERT INTO @Continents(Continent, Countries) VALUES('Africa', 57) INSERT INTO @Continents(Continent, Countries) VALUES('North America', 23) INSERT INTO @Continents(Continent, Countries) VALUES('South America', 12) INSERT INTO @Continents(Continent, Countries) VALUES('Antarctica', 0) INSERT INTO @Continents(Continent, Countries) VALUES('Europe', 50) INSERT INTO @Continents(Continent, Countries) VALUES('Australia', 3) |
List of Continents concatenated using a comma delimiter and sorted alphabetically:
|
1 2 3 4 |
SELECT SQLHTTP.net.Group_Concat(Continent, ',', Continent) FROM @Continents |
|
1 2 3 4 |
---------------------------------------------------------------------- Africa,Antarctica,Asia,Australia,Europe,North America,South America |
List of Continents concatenated using a comma delimiter and sorted by Countries ascending:
|
1 2 3 4 |
SELECT SQLHTTP.net.Group_Concat(Continent, ',', Countries) FROM @Continents |
|
1 2 3 4 |
---------------------------------------------------------------------- Antarctica,Australia,South America,North America,Asia,Europe,Africa |
List of Continents concatenated using a comma delimiter and sorted by Countries descending order:
|
1 2 3 4 |
SELECT SQLHTTP.net.Group_Concat(Continent, ',', 100 - Countries) FROM @Continents |
|
1 2 3 4 |
---------------------------------------------------------------------- Africa,Europe,Asia,North America,South America,Australia,Antarctica |