CODEWITHSUNDEEP
sql-server-2008
Rudolf Lamprecht
Rudolf Lamprecht

Reputation: 1070

Combine column value of multiple rows into 1

I need to combine the column result of multiple rows into one variable. A sample of my dataset can be found below.

EmailAddress
-------------
[email protected]
[email protected]

Currently, I am using STUFF to combine the EmailAddress column into a semicolon delimited list. A sample can be seen below.

SELECT @EmailAddress = CONVERT(nvarchar(max)    
              , STUFF(
                  (SELECT ';' + EmailAddress FROM EmployeeCourseDetails)
                , 1
                , 1
                , '')
              )

The desired result should look like [email protected];[email protected] but when I execute the statement, I get the following error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Any help please?

Upvotes: 1

Views: 1619

Answers (2)

sekky
sekky

Reputation: 834

I did something similar once with FOR XML PATH(''). Like this: 

SELECT @EmailAddress = CONVERT(nvarchar(max)    
              , STUFF(
                  (SELECT ';' + EmailAddress FROM EmployeeCourseDetails FOR XML PATH(''))
                , 1
                , 1
                , '')
              )

Upvotes: 1

Tim Schmelter
Tim Schmelter

Reputation: 460048

You can use COALESCE:

DECLARE @EmailAddress VARCHAR(8000) 

SELECT   @EmailAddress = COALESCE(@EmailAddress + ';', '') + EmailAddress
FROM     EmployeeCourseDetails
ORDER BY EmailAddress

SELECT @EmailAddress

DEMO

Result: [email protected];[email protected]

Upvotes: 1

Related Questions