Retrieving SQL Relationships as a comma delimited string

Question

I have a SQL Server 2005 database with two tables: Order, LineItem. Each LineItem has a field called LineItemID and OrderID. I have a query that is getting all of the Order records in my database. With each Order record, I would like to retrieve a comma delimited list of LineItemIDs associated with the Order.

Is there a way to do this in SQL? I do not know how to do this.

Thank you!

Answer 1

Here's one example, using the name column from sys.tables, of how to construct a comma-delimited string from a column:

use master
go

SELECT Stuff((SELECT ',' + name
              FROM sys.tables
              For XML PATH ('')),1,1,'')
go