CODEWITHSUNDEEP
sqlsybasesap-ase
mani_nz
mani_nz

Reputation: 5592

Combine 2 rows into 1 row - Sybase

I have a resultset as below,

id fname  lname
11 Tom   Jerry
11 Tom   Harry

Is there a way I could merge both the rows into 1 row, and lname value merged with comma seperated. Like below,

11 Tom   Jerry,Harry

Cheers!!

Upvotes: 2

Views: 3609

Answers (2)

Nishad
Nishad

Reputation: 426

steps : (Pseudo code)

1 - get base table data in order by id,fname

2 - process each row in cursor and use below logic till id,fname values are same once it gtes changed , control should come out of loop to insert new set of values(id,fname) appended by comma separated lastname

BEGIN
  SET @List = @List + ',' + Cast(@lname As varchar(10)) 
END

Upvotes: 0

Rich Benner
Rich Benner

Reputation: 8113

You can do this with Stuff and some XML working. Edit: Not tested on sybase but give it a go and see

Test Data

CREATE TABLE #TestData (id int, fname varchar(20), lname varchar(20))
INSERT INTO #TestData 
VALUES
 (11,'Tom','Jerry')
,(11,'Tom','Harry')

Query

SELECT 
a.id
,a.fname
,STUFF((SELECT ',' + b.lname
        FROM #TestData b 
        WHERE b.id = a.id 
        FOR XML PATH('')), 1, 1, '') lname
FROM #TestData a
GROUP BY a.id, a.fname

Result

id  fname   lname
11  Tom     Jerry,Harry

Upvotes: 1

Related Questions