CODEWITHSUNDEEP
sqlsql-server
w0051977
w0051977

Reputation: 15787

Return two rows as a single row

Please see the SQL DDL below:

create table dbo.Test(id int, name varchar(30))

INSERT INTO Test values (1, 'Mark') 
INSERT INTO Test values (2,'Williams')

I am trying to return: 'Mark Williams' using an SQL SELECT. I have tried using an SQL Pivot, but it has not worked.

Upvotes: 0

Views: 64

Answers (3)

Karl Kieninger
Karl Kieninger

Reputation: 9129

Possibly more flexible than COALESCE would be to use the STUFF and FOR XML pattern:

SELECT TOP 1
       STUFF((SELECT ' ' + Name AS [text()]
                FROM dbo.Test 
               ORDER BY id
                 FOR XML PATH('')), 1, 1, '' ) Concatenated
 FROM TEST

Upvotes: 1

huMpty duMpty
huMpty duMpty

Reputation: 14460

DECLARE @NameList VARCHAR(8000) 
SELECT @NameList = COALESCE(@NameList + ' ', '') + Name 
FROM Test
SELECT @NameList

Read more COALESCE

Upvotes: 1

rory.ap
rory.ap

Reputation: 35260

Try this:

DECLARE @Return VARCHAR(MAX)

SELECT @Return = COALESCE(@Return+' ','') + name
FROM dbo.TEST

SELECT @Return

Upvotes: 1

Related Questions