Less Than Dot is a community of passionate IT professionals and enthusiasts dedicated to sharing technical knowledge, experience, and assistance. Inside you will find reference materials, interesting technical discussions, and expert tips and commentary. Once you register for an account you will have immediate access to the forums and all past articles and commentaries.

Your profile

Search

Views

Concatenate Values From Multiple Rows Into One Column Ordered

From Wiki

Jump to: navigation, search

If you want to concatenate values from multiple rows into one and you want to order it then you have to use FOR XML PATH. The ORDER BY is -not- guaranteed to be processed before the concatenation occurs, if you use that technique. However, it -is- guaranteed if you use FOR XML PATH('').

Let's take a look. First create these tables:

tsqlLine number Off | Hide | Select all

USE TEMPDB
GO
CREATE TABLE Authors (Id INT, LastName VARCHAR(100), FirstName VARCHAR(100))
GO
INSERT Authors VALUES(1,'Nielsen','Paul')
INSERT Authors VALUES(2,'King','Stephen')
INSERT Authors VALUES(3,'Stephenson','Neal')
GO
CREATE TABLE Books (ID INT,AuthorID INT, BookName VARCHAR(200))
GO
INSERT Books VALUES(1,1,'SQL Server 2005 Bible')
INSERT Books VALUES(2,1,'SQL Server 2008 Bible')
INSERT Books VALUES(3,2,'It')
INSERT Books VALUES(4,2,'The Stand')
INSERT Books VALUES(5,2,'Thinner')
INSERT Books VALUES(6,2,'Salems Lot')
INSERT Books VALUES(7,3,'Snow Crash')
INSERT Books VALUES(8,3,'The Diamond Age')
INSERT Books VALUES(9,3,'Cryptonomicon')
GO

This is the old style function

tsqlLine number Off | Hide | Select all

CREATE FUNCTION fnGetBooks2 (@AuthorID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @BookList VARCHAR(8000)
SELECT @BookList = ''
SELECT @BookList = @BookList + BookName +','
FROM Books
WHERE AuthorID = @AuthorID
AND BookName IS NOT NULL
ORDER BY BookName --yes we can sort
RETURN LEFT(@BookList,(LEN(@BookList) -1))
END
GO

This is the same function using XML Path

tsqlLine number Off | Hide | Select all

CREATE FUNCTION fnGetBooks (@AuthorID INT)
RETURNS VARCHAR(8000)
AS
BEGIN
DECLARE @BookList VARCHAR(8000)
SELECT @BookList =(
SELECT BookName + ', ' AS [TEXT()]
FROM Books
WHERE AuthorID = @AuthorID
AND BookName IS NOT NULL
ORDER BY BookName
FOR XML PATH('') )
RETURN LEFT(@BookList,(LEN(@BookList) -1))
END
GO

tsqlLine number Off | Hide | Select all

SELECT *,dbo.fnGetBooks(id) AS Books
FROM Authors
Results

tsqlLine number Off | Hide | Select all

SELECT *,dbo.fnGetBooks2(id) AS Books
FROM Authors
Results

Id	LastName	FirstName	Books
1	Nielsen	Paul	SQL Server 2005 Bible,SQL Server 2008 Bible
2	King	Stephen	It,Salems Lot,The Stand,Thinner
3	Stephenson	Neal	Cryptonomicon,Snow Crash,The Diamond Age