concatenate columns with comma in between

I'm using sql server 2008.

I have a table that looks like this:

TestTable1.JPG
This is the script to create the table:

USE [TestDatabase]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestOrders1](
	[OrderID] [int] NOT NULL,
	[LastName1] [varchar](50) NULL,
	[LastName2] [varchar](50) NULL,
	[LastName3] [varchar](50) NULL,
	[LastName4] [varchar](50) NULL,
	[LastName5] [varchar](50) NULL
) ON [PRIMARY]

GO
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (1, N'Smith', N'', N'Johnson', N'', N'')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (2, N'Rogers', N'', N'', N'Taylor', N'Williams')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (3, N'Smithers', N'Phillips', N'', N'', N'Lee')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (4, N'', N'Jackson', N'', N'', N'Chen')
INSERT [dbo].[TestOrders1] ([OrderID], [LastName1], [LastName2], [LastName3], [LastName4], [LastName5]) VALUES (5, N'Rainier', N'', N'Grisham', N'Jacobs', N'Bly')

Open in new window


I would like to create a column called TestLastnames which is is all the lastname columns seperated by a comma.

My desired result would look like like this column colored in blue:

TestTable1Desired.JPG
What's the best syntax to create this column?
LVL 1
maqskywalkerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
Bill PrewIT / Software Engineering ConsultantCommented:
Here's a simple approach.

SELECT *,
    CASE WHEN [LastName1] = '' THEN '' ELSE [LastName1]+',' END + 
    CASE WHEN [LastName2] = '' THEN '' ELSE [LastName2]+',' END + 
    CASE WHEN [LastName3] = '' THEN '' ELSE [LastName3]+',' END + 
    CASE WHEN [LastName4] = '' THEN '' ELSE [LastName4]+',' END +
    CASE WHEN [LastName5] = '' THEN '' ELSE [LastName5] END AS TestLastNames
FROM [TestOrders1]

Open in new window


»bp
Scott PletcherSenior DBACommented:
For a fixed number of columns, I'd avoid the complexity of XML:

STUFF(
    CASE WHEN LastName1 > '' THEN ', ' + LastName1 ELSE '' END +
    CASE WHEN LastName2 > '' THEN ', ' + LastName2 ELSE '' END +
    CASE WHEN LastName3 > '' THEN ', ' + LastName3 ELSE '' END +
    CASE WHEN LastName4 > '' THEN ', ' + LastName4 ELSE '' END +
    CASE WHEN LastName5 > '' THEN ', ' + LastName5 ELSE '' END
, 1, 2, '')

Testing for the presence of data is cleaner than checking for the absence of it, since it takes care of a NULL value(s) as well.
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

slightwv (䄆 Netminder) Commented:
I would hope the actual table design isn't that denormalized and what is presented is the output of a query.

If you have a table like that, I would really look at normalizing it.
maqskywalkerAuthor Commented:
Bill,

That's very close.

I ran your query. This is the result.

Test1.JPG
The result shows a comma after Johnson.
There shouldn't be a comma after Johnson.
maqskywalkerAuthor Commented:
that is not a real table. that is just a sample table only showing the last name column in question.
maqskywalkerAuthor Commented:
i got this

SELECT [OrderID]
      ,[LastName1]
      ,[LastName2]
      ,[LastName3]
      ,[LastName4]
      ,[LastName5]
      ,COALESCE([LastName1], '') + COALESCE([LastName2], '') + COALESCE([LastName3], '') + COALESCE([LastName4], '') + COALESCE([LastName5], '') AS TestLastName
FROM [TestDatabase].[dbo].[TestOrders1]

Open in new window


and this is the output.

But now how do i put a comma in between the lastnames?

Sample1.JPG
slightwv (䄆 Netminder) Commented:
If you have a table with a single last name column, check out the XML trick in the link I posted.
maqskywalkerAuthor Commented:
slightwv,

I tried your sample but that is concatenating the rows of column1

I want to concatenate the values in column [LastName1],[LastName2],[LastName4],[LastName4],[LastName5] into a new column called [TestLastName]
slightwv (䄆 Netminder) Commented:
Yes, my example converts rows to a csv.  I guessed that is what you wanted.

If you truly want columns to csv, I would look at the one Scott posted.

As you know, coalesce won't work since it just returns the first not null value in a list of values.
Bill PrewIT / Software Engineering ConsultantCommented:
Try this.  If you know for sure there will never be spaces in the last names we could simplify slightly, but I didn't want to make that assumption so protected for it.

SELECT *,
    REPLACE(REPLACE(RTRIM(CASE WHEN [LastName1] = '' THEN '' ELSE REPLACE([LastName1],' ','|')+' ' END + 
    CASE WHEN [LastName2] = '' THEN '' ELSE REPLACE([LastName2],' ','|')+' ' END + 
    CASE WHEN [LastName3] = '' THEN '' ELSE REPLACE([LastName3],' ','|')+' ' END + 
    CASE WHEN [LastName4] = '' THEN '' ELSE REPLACE([LastName4],' ','|')+' ' END +
    CASE WHEN [LastName5] = '' THEN '' ELSE REPLACE([LastName5],' ','|') END), ' ', ','),'|',' ') AS TestLastNames
FROM [TestOrders1]

Open in new window


»bp
Scott PletcherSenior DBACommented:
Again, I'd be careful with a test like this:

CASE WHEN [LastName1] = ''

because if the value is NULL, that condition will not be true and the ELSE condition will be performed, concatenating a NULL into the values, which will automatically force the entire value to be NULL, viz:

SELECT
    case when name1 = '' then '' else ', ' + name1 end +
    case when name2 = '' then '' else ', ' + name2 end +
    case when name3 = '' then '' else ', ' + name3 end +
    case when name4 = '' then '' else ', ' + name4 end +
    case when name5 = '' then '' else ', ' + name5 end
FROM ( VALUES('a','b','c',null,'e'),('z','y','x','w','v') ) as names(name1,name2,name3,name4,name5)

Vs:

SELECT STUFF(
    case when name1 > '' then ', ' + name1 else '' end +
    case when name2 > '' then ', ' + name2 else '' end +
    case when name3 > '' then ', ' + name3 else '' end +
    case when name4 > '' then ', ' + name4 else '' end +
    case when name5 > '' then ', ' + name5 else '' end, 1, 2, '') AS name_list
FROM ( VALUES('a','b','c',null,'e'),('z','y','x','w','v') ) as names(name1,name2,name3,name4,name5)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bill PrewIT / Software Engineering ConsultantCommented:
Agree that depending on the data some adjustments to handle nulls versus empty strings etc might be needed.  I made my suggestion based on the test data provided, which did not contain nulls, but rather contained empty strings for "missing" values.


»bp
Scott PletcherSenior DBACommented:
I get that, but later they could choose to use NULLs instead of empty values.  You don't want your code to fail down the road because of that.
Bill PrewIT / Software Engineering ConsultantCommented:
Adjusted to handle NULL if present...

SELECT *,
    REPLACE(REPLACE(RTRIM(
        CASE WHEN [LastName1] > '' THEN REPLACE([LastName1],' ','|')+' ' ELSE '' END + 
        CASE WHEN [LastName2] > '' THEN REPLACE([LastName2],' ','|')+' ' ELSE '' END + 
        CASE WHEN [LastName3] > '' THEN REPLACE([LastName3],' ','|')+' ' ELSE '' END + 
        CASE WHEN [LastName4] > '' THEN REPLACE([LastName4],' ','|')+' ' ELSE '' END +
        CASE WHEN [LastName5] > '' THEN REPLACE([LastName5],' ','|') ELSE '' END
    ), ' ', ','),'|',' ') AS TestLastNames
FROM [TestOrders1]

Open in new window

UPDATED: Corrected first field handling to match other fields and work properly.


»bp
maqskywalkerAuthor Commented:
thanks. Good stuff guys.
Scott PletcherSenior DBACommented:
FYI, just so you'll be aware:

SELECT *,
    REPLACE(REPLACE(RTRIM(CASE WHEN [LastName1] = '' THEN '' ELSE REPLACE([LastName1],' ','|')+' ' END +
    CASE WHEN [LastName2] > '' THEN REPLACE([LastName2],' ','|')+' ' ELSE '' END +
    CASE WHEN [LastName3] > '' THEN REPLACE([LastName3],' ','|')+' ' ELSE '' END +
    CASE WHEN [LastName4] > '' THEN REPLACE([LastName4],' ','|')+' ' ELSE '' END +
    CASE WHEN [LastName5] > '' THEN REPLACE([LastName5],' ','|') ELSE '' END), ' ', ','),'|',' ') AS TestLastNames
FROM ( VALUES(null,'b','c','d','e') ) as x( LastName1, LastName2, LastName3, LastName4, LastName5 )
Bill PrewIT / Software Engineering ConsultantCommented:
Thanks Scott, obviously I forgot to correct the first field when I did the other 4, doh.  I updated my prior comment to correct this and noted it.


»bp
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.