Sql combine 2 rows to one

Question

Hi ,

i have this table Testers

employee name
------------
Sam Korch
dan mano

i want to combine tow rows to one, it will be "Sam Korch,Dan Mano"

i have this query

select @theString = COALESCE(@theString + ',', '') + EmployeeName 
  from Testers join vw_EKDIR on Testers.TesterGlobalId = vw_EKDIR.GlobalID
 where TestId = 31   

it working but i dont want to do select i want the result will be in @thestring

so i try to do this query

set @theString = (
    select  @theString = COALESCE(@theString + ',', '') + EmployeeName 
      from Testers join vw_EKDIR on Testers.TesterGlobalId = vw_EKDIR.GlobalID
     where TestId = 31 
    )

it is not working ... i want @thestring will be the result.

any idaes ?

thanks

Answer 1

For the sql-part, doesn't something like this work:

SELECT t1.name + ", " + t2.name 
FROM tester t1, 
     tester t2
WHERE t1.TesterGlobalId == t2.GlobalId
AND t1.testId = 31;

I don't know the specific rg-sql-server/rg-tsql.

Answer 2

I have this function and it work

    ALTER FUNCTION [dbo].[udf_concat] ( @c INT )
            RETURNS VARCHAR(MAX) AS BEGIN
            DECLARE @p VARCHAR(MAX) ;
            SET @p='';
                SELECT    @p = @p + ISNULL(T_NAME.T_FIELD,'')+'|'
                FROM         T_NAME 
where T_NAME.T_OTHER_FIELD=@c

            RETURN @p
            END

Answer 3

For SQL Server 2005+, you can use FOR XML PATH and STUFF function to return a concatenated list:

SELECT @theString = STUFF(SELECT ','+ vwe.employeename
                            FROM TESTERS t
                            JOIN VW_EKDIR vwe ON vwe.globalid = t.testerglobalid
                           WHERE t.testid = ?
                         FOR XML PATH('')), 1, 1, '')

Answer 4

set @theString = (select COALESCE(@theString + ',', '') + EmployeeName from Testers join vw_EKDIR on Testers.TesterGlobalId = vw_EKDIR.GlobalID where TestId = 31 )