CODEWITHSUNDEEP
sqlsql-servert-sql
Yan
Yan

Reputation: 1444

Sql combine 2 rows to one

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

Upvotes: 0

Views: 404

Answers (4)

msi77
msi77

Reputation: 1632

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

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332571

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, '')

Upvotes: 1

Flatlineato
Flatlineato

Reputation: 1066

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

Upvotes: 1

user unknown
user unknown

Reputation: 36229

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.

Upvotes: 0

Related Questions