SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server and T-SQL Development Tutorials
Development resources, articles, tutorials, code samples and tools and downloads for ASP.Net, SQL Server, R Script, Windows, Windows Phone, AWS, SAP HANA and ABAP, like SAP UI5, Screen Personas, etc.




Install SQL Server 2019
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014



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





Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

MS SQL Server Forums









Copyright © 2004 - 2019 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems