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?

Answer 1

Look here:

Answer 2

duplicate question

http://stackoverflow.com/questions/122942/how-to-return-multiple-values-in-one-column-t-sql/123025

Answer 3

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 4

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