subquery returned more than 1 value - return multiple results as comma delimited

You may want to do a subquery to get data from another table, but you realize there could be more than one record. If you do a normal subquery in your SELECT statement, you will get the following error.

Error

subquery returned more than 1 value

Solution

You can use STUFF ... FOR xml path to return multiple records as a comma delimited single result. 

SELECT [EmployeeUid]

      ,[FirstName]

      ,[LastName]

      ,[Email]

      ,[Phone]

  ,STUFF(

(  

SELECT distinct ',' + convert(varchar(10),LocationId)

FROM [dbo].[Offices] a where a.EmployeeUid = g.EmployeeUid

FOR xml path('')

)

                        , 1

                        , 1

                        , '') as Locations

  FROM [dbo].[Employees] g