Group records and put results into single field

Question

I have the following recordset:

ID      AssetName
882     Training Room VCR
882     Training Room DVD
882     Training Room TV
858     Training Room VCR
858     Training Room Computer
858     Training Room TV

I want to group each ID and get a resultset as such:

ID      Assets
882     Training Room VCR<br/>Training Room DVD<br/>Training Room TV
858     Training Room VCR<br/>Training Room Computer<br/>Training Room TV   

Is there a way to build a SQL 2005 statement that will do this?

---

duplicate question

How to return multiple values in one column (T-SQL)?

Answer 1

I recommend keeping the logic for adding the <br /> elements in your server-side code, not the SQL query. Keep your SQL focused on returning the result set and use loops in your code to figure out how to present it like that.

A first attempt might involve a query-based loop with another query-based loop inside it, but that will result in a bombardment of queries on your database. It's better to use a single large query result and have logic branches for conditional output. Modify your query to facilitate the desired output, not create it. For instance, you might order the data so you know you'll be getting all the assets of one ID before moving on to the next.

Answer 2

This will get what you need.

SELECT DISTINCT
    [ID]
    ,CONVERT(VARCHAR(MAX),
    (   SELECT [AssetName] [div]
        FROM [dbo].[tableName] t1 
        WHERE t1.[ID] = t.[ID] FOR XML PATH('')
    )) [asset]
FROM [dbo].[tableName] t