以逗号分隔的字符串形式检索SQL关系

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.

我有一个SQL Server 2005数据库，包含两个表:Order、LineItem。每个LineItem都有一个名为LineItemID和OrderID的字段。我有一个查询，它获取数据库中的所有订单记录。对于每个订单记录，我希望检索一个由逗号分隔的与订单相关的lineitemid列表。

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

在SQL中有这样的方法吗?我不知道该怎么做。

Thank you!

谢谢你！

1 个解决方案

#1

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

这里有一个例子，使用来自sys的name列。表，说明如何从列构造以逗号分隔的字符串:

use master
go

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