Using SELECT to Concatenate Strings Across Rows

Most of use SELECT to return columns, maybe we do some concatention to build somethng like full name out of first name and last name. Today we'll go beyond that, showing you how to add a string variable to the select in a way that will let you build strings that are comprised of values from many rows, and we'll use that trick to build a comma delimited list that you could save to a text file or use as the input for an IN operation.

Andy Warren is a software trainer focusing on SQL Server, a member of the PASS Board of Directors, and a principal in this site - SQLShare.com.

Albert on 10/21/2010

Really good as usual, muy bueno como siempre

James Lim on 10/21/2010

Good content. I don't use concatenation that often, so I find this helpful as a refresher as well.

Duane on 10/21/2010

good trick

Eric Moreau on 10/21/2010

instead of replacing ' ' with ' ', you should have use: firstname + ' ' + isnull(left(middlename, 1) + ' ', '') + lastname

Stephen Oesterreicher on 10/21/2010

You can use a Case Statement: SELECT (RTRIM(dbo.NameHistoryTable.FirstName) + CASE MiddleInitial WHEN ' ' THEN ' ' ELSE ' ' + RTRIM(MiddleInitial) + '. ' END + RTRIM(dbo.NameHistoryTable.LastName)) AS FullName FROM dbo.NameHistoryTable

Yelena Varshal on 10/21/2010

Very good!

1F05990EE7 on 10/21/2010

Hi There, Well, instead of replacing double spaces by single space for @contactlist variable, one can do the following: SELECT @a = @a + FirstName + ' ' + ISNULL(Left(MiddleName, 1) + ' ', '') + LastName + ', ' and then you can discard the trailing , from @a and that's it. You don't have to use replace function at all. Warm Regards, Umesh Bhavsar

Alexander on 10/21/2010

very interesting

Jamshid Nouri on 10/21/2010

excellent demo

Gene on 10/21/2010

helpful

sam_shenouda on 10/22/2010

this is great. I have done at my job and it took a little while to figure it out

Terrance on 10/22/2010

Nice video

Dexter Jones on 10/22/2010

Slick. Nice tip about initializing the varchar(MAX) variable. Thanks!

Lerma Winchell on 10/22/2010

Very nice tip.

David Stark on 10/22/2010

Cool trick with concatenating strings!

Ed K on 10/22/2010

Solution left trailing ,

Tom Uellner on 10/24/2010

Perhaps it could be a little more concise using COALESCE()? DECLARE @ContactList VARCHAR(MAX) SET @ContactList = '' SELECT TOP 50 @ContactList = @ContactList + FirstName + ' ' + COALESCE(LEFT(MiddleName, 1) + ' ', '') + LastName + ',' FROM Person.Contact SELECT @ContactList

Andrew on 10/25/2010

Good tips.

WChaster on 10/26/2010

This would be great for making CSV files too, but you need to remove the trailing comma.

Daniel Wolford on 10/26/2010

Surprised you didn't mention this trick's usefullness in avoiding a cursor to do the same work, and how much more efficient it is. Also, this trick is proprietary to SQL Server, from what I have read..i.e. Oracle can't do it! :)

James Millican on 10/28/2010

could have used coalesce

mark mcnary on 11/4/2010

As someone who uses a lot of CTEs, this is gong to come in handy building IN lists to be used in a lower CTE.

Jason P. Brown on 11/10/2010

Need to bump up the volume and have presenter keep voice dynamics level throughout presentation.

nilesh bilimoria on 11/16/2010

nice job

Edward on 2/2/2011

very good.