Starting with SQL Server 2017, you can now make your query results appear as a list. This means you can have your result set appear as a comma-separated list, a space-separated list, or whatever separator you choose to use.While it’s true that you could achieve this same effect prior to SQL Server 2017, it was a bit fiddly.now has the STRINGAGG function, which concatenates the values of string expressions and places separator values between them.
Summary: in this tutorial, you will learn how to use the SQL CONCAT function to concatenate two or more strings into a single string. Introduction to SQL CONCAT function. The SQL CONCAT function concatenates two or more strings into one string.The following illustrates the syntax of the CONCAT function. Starting with SQL Server 2017, you can now make your query results appear as a list. This means you can have your result set appear as a comma-separated list, a space-separated list, or whatever separator you choose to use. While it’s true that you could achieve this same effect prior to SQL Server 2017, it was a bit fiddly. Jun 6, 2019 - Concatenate Multiple Rows Within Single Row in SQL Server 2008. The final result will display all the studentids and courseids separated.
This works in much the same way to MySQL’s function.This article provides examples that demonstrate the T-SQL STRINGAGG function.Sample DataFirst, here’s some sample data. SELECT TaskId, TaskNameFROM Tasks;Result: TaskId TaskName- -1 Feed cats2 Water dog3 Feed garden4 Paint carpet5 Clean roof6 Feed catsExample – Comma Separated ListSo we could take the above data, and use the STRINGAGG function to list all the task names in one big comma separated list.Like this: SELECT STRINGAGG(TaskName, ', ')FROM Tasks;Result: Feed cats, Water dog, Feed garden, Paint carpet, Clean roof, Feed catsOf course, it doesn’t necessarily need to be separated by a comma. It can be separated by any expression of NVARCHAR or VARCHAR type, and it can be a literal or a variable. Example – Combining ColumnsWe could also use the to combine two fields together, separated by their own separator.Example: SELECT STRINGAGG(CONCAT(TaskId, ') ', TaskName), ' ')FROM Tasks;Result: 1) Feed cats 2) Water dog 3) Feed garden 4) Paint carpet 5) Clean roof 6) Feed catsExample – Null ValuesIf your result set contains null values, those values are ignored and a corresponding separator is not added.If this isn’t suitable, you can provide a value for null values by using the ISNULL function and passing in the value you’d like to use whenever a null value is encountered.
I'm trying to achieve the following: California Los Angeles, San Francisco, SacramentoFlorida Jacksonville, MiamiUnfortunately, I'm getting ',Los Angeles, San Francisco, Sacramento, Jacksonville, Miami'I can achieve my desired results using the STUFF function, but was wondering if there's a cleaner way of doing it using COALESCE? STATE CITYCalifornia San FranciscoCalifornia Los AngelesCalifornia SacramentoFlorida MiamiFlorida JacksonvilleDECLARE @col NVARCHAR(MAX);SELECT @col= COALESCE(@col, ') + ',' + cityFROM tbl where city = 'California';SELECT @col;Thanks. This might be the cleaner approach you're after. Basically, check if the variable has been initialized yet.
If it hasn't, set it to the empty string, and append the first city (no leading comma). If it has, then append a comma, then append the city. DECLARE @col nvarchar(MAX);SELECT @col = COALESCE(@col + ',', ') + cityFROM dbo.tbl WHERE state = 'California';Of course, that only works for populating a variable per state. Just to add to above.Be aware that an ORDER BY may break by only including the last item in your query. In my case, I was not grouping, so not sure if that makes a difference.
I'm using SQL 2014. In my case, I have something like value1, value2, value3. But my result in the variable was only value3.Aaron commented to say:This has been reported at least four times on Connect:.Example response from Microsoft:The behavior you are seeing is by design.
Sql Concat String Columns
Using assignment operations (concatenation in this example) in queries with ORDER BY clause has undefined behavior.The response also references KB 287515:The solution is to use FOR XML PATH (the second approach in Aaron's answer) if the order of concatenation is important and, of course, if you want to be sure to include all values. Also see:on Stack Overflow.