Concat Field Value to String in SQL Server

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

How to concatenate string message with column value in SQL Server

Remove the Double quotes and last + operator

Select 'The Maximum Rating of the city '+city+' is = '+ cast(MAX(rating) as varchar(50))
from CUSTOMER 
where CITY is not null 
group by CITY;

If you are using Sql Server 2012+ then you can use Concat function which does not require explicit conversion

Select Concat('The Maximum Rating of the city ',city,' is = ', MAX(rating))
from CUSTOMER 
where CITY is not null 
group by CITY;

How do I concatenate text in a query in sql server?

The only way would be to convert your text field into an nvarchar field.

Select Cast(notes as nvarchar(4000)) + 'SomeText'
From NotesTable a

Otherwise, I suggest doing the concatenation in your application.

concat two integers and result as string in SQL

You can CAST your integer field to varchar and then concatenate them as you want.

DECLARE @ID INT 
DECLARE @Number INT 

SET @ID = 101 
SET @Number = 9  

SELECT CAST(@ID AS VARCHAR(10) ) +'.'+ CAST(@Number AS VARCHAR(10) ) 

SQL: Concatenate column values in a single row into a string separated by comma

I think this takes care of all of the issues I spotted in other answers. No need to test the length of the output or check if the leading character is a comma, no worry about concatenating non-string types, no significant increase in complexity when other columns (e.g. Postal Code) are inevitably added...

DECLARE @x TABLE(Id INT, City VARCHAR(32), Province VARCHAR(32), Country VARCHAR(32));

INSERT @x(Id, City, Province, Country) VALUES
(1,'Vancouver','British Columbia','Canada'),
(2,'New York' , null             , null   ),
(3, null      ,'Adama'           , null   ),
(4, null      , null             ,'France'),
(5,'Winnepeg' ,'Manitoba'        , null   ),
(6, null      ,'Quebec'          ,'Canada'),
(7,'Seattle'  , null             ,'USA'   );

SELECT Id, Location = STUFF(
      COALESCE(', ' + RTRIM(City),     '') 
    + COALESCE(', ' + RTRIM(Province), '') 
    + COALESCE(', ' + RTRIM(Country),  '')
    , 1, 2, '')
  FROM @x;

SQL Server 2012 added a new T-SQL function called CONCAT, but it is not useful here, since you still have to optionally include commas between discovered values, and there is no facility to do that - it just munges values together with no option for a separator. This avoids having to worry about non-string types, but doesn't allow you to handle nulls vs. non-nulls very elegantly.

Concatenate Constant value in string in where or Order by clause in sql server?

I found what i am doing wrong. In Sql Server we use single inverted comma (') for constant expression not double inverted comma (").
So, It will be

SELECT [rid] ,[name], [billsofmonth] FROM [reports] order by CAST ( ('1-' + billsofmonth) as datetime)

Concatenate certain values according to their keys using case

If I understand correctly, you have a dynamic list of keys such as name, age, hight and you want to display those values per user.

You need to use aggregation and string_agg:

select id, string_agg(
    "value", ','
) within group (order by charindex(',' + "key" + ',', ',name,age,hight,')) as csv
from t
where "key" in ('name', 'age', 'hight')
group by id

---

Related Topics