CODEWITHSUNDEEP
sql-servert-sql
ashishduh
ashishduh

Reputation: 6699

Combining multiple records into one

I'm using the following query:

DECLARE @Code varchar(6)

SELECT a.Code, a.Description, a.Time, b.id
FROM TableA a
LEFT OUTER JOIN TableB B ON a.id = b.id
WHERE a.Code = @Code

The issue I'm having is it's returning multiple records because of the outer join, something like the following:

Code    Description     Time    B.id
5038    sample desc     4       108
5038    sample desc     4       632
5038    sample desc     4       633
5038    sample desc     4       197
5038    sample desc     4       503

What would be the best way to combine these into one record, with say a delimited list of b.id's?

Upvotes: 0

Views: 101

Answers (2)

Kenneth Garza
Kenneth Garza

Reputation: 1916

You can use this method found here 

USE AdventureWorks
GO

DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO

Upvotes: 0

Sonam
Sonam

Reputation: 3466

For a delimited list you can go for XML Path clause. Here is the explanation: http://blog.sqlauthority.com/2013/04/05/sql-server-group-by-rows-and-columns-using-xml-path-efficient-concating-trick/

Upvotes: 1

Related Questions