SQL concatenation : SQL Concatenate Strings Function for MS SQL Server with T-SQL
T-SQL developers fruequently deal with sql concatenation and with sql concatenate strings functions.
As a sql developer I will try to give samples of sql concatenation example on MS SQL Server with t-sql.
SQL concatenation is being used since early versions of MS SQL Server.
For example for SQL Server 2000, you can sql concatenate strings using COALESCE function.
With MS SQL Server 2005 and T-SQL improvements, sql developers or database administrators has new t-sql concatenation functions and methods.
String concatenation in sql can be implemented by using still COALESCE function, as well as new string concatenation methods using FOR XML PATH().
Sample sql tables create code and populate example tables with sample data.
You can execute the below t-sql script in order to create the sql data tables with its sample table rows which we will use later in the tutorial for sql concatenation.
CREATE TABLE Courses
(
CourseId int,
Title nvarchar(100)
);
CREATE TABLE Students
(
StudentId int,
StudentName nvarchar(100)
);
CREATE TABLE StudentCourses
(
StudentId int,
CourseId int
);
insert into Students select 1, 'Bill Gates'
insert into Students select 2, 'Steve Ballmer'
insert into Students select 3, 'Larry Ellison'
insert into Courses select 1, 'Software Engineering'
insert into Courses select 2, 'Operating Systems'
insert into Courses select 3, 'Data Management'
insert into StudentCourses select 1,1
insert into StudentCourses select 1,2
insert into StudentCourses select 2,1
insert into StudentCourses select 2,3
insert into StudentCourses select 3,1
insert into StudentCourses select 3,2
insert into StudentCourses select 3,3
SQL Concatenation on MS SQL Server 2005 and SQL Server 2008 using FOR XML PATH() T-SQL Function
With the new XML functions in T-SQL introduced with Microsoft SQL Server 2005, sql string concatenation can be now implemented using FOR XML PATH t-sql statements as follows :
SELECT
S.StudentId,
S.StudentName,
STUFF(
(
SELECT
',' + Title
FROM Courses C
INNER JOIN StudentCourses SC
ON C.CourseId = SC.CourseId
WHERE SC.StudentId = S.StudentId
FOR XML PATH('')
), 1, 1, ''
) As concatenated_string
FROM Students S
SQL Concatenation on MS SQL Server 2000 using COALESCE T-SQL Function
Here is a string concatenation function using T-SQL Coalesece function in order to sql concatenate course titles for a given student.
CREATE FUNCTION fx_SQLConcatenateStringsFunction
(
@prmId int
)
RETURNS NVARCHAR(4000)
AS
BEGIN
DECLARE @SQLConcatenation nvarchar(4000)
-- Main t-sql concatenation takes place here
SELECT
@SQLConcatenation =
COALESCE(@SQLConcatenation,',') + Courses.Title + ','
FROM StudentCourses (NoLock)
INNER JOIN Courses (NoLock)
ON Courses.CourseId = StudentCourses.CourseId
WHERE StudentCourses.StudentId = @prmId
-- Format sql concatenation output
IF @SQLConcatenation IS NOT NULL
SET @SQLConcatenation = SUBSTRING(@SQLConcatenation,2,LEN(@SQLConcatenation)-2)
RETURN @SQLConcatenation
END