sql - SQL 语句中的嵌套循环

Question

我一直在试图弄清楚是否有一种方法可以在 SQL 语句中使用循环。

Select j.JobID, s.OrderNumber, 
(SELECT OperationID From Operations) as Operations  <--- This will cause multiple results
From Jobs j
Inner Join Sales s on j.JobID = s.SalesJobID
Where j.JobID = '123456-1-1'

这就是我们所拥有的

JobID        OrderNumber    Operation
-----        -----------    ----------
1123456-1-1   TEST1         DEV
1123456-1-1   TEST1         APR
1123456-1-1   TEST1         CAT
1123456-1-1   TEST1         LFT
1123456-1-1   TEST1         PKG

试图做这样的事情。

JobID        OrderNumber    Operations
-----        -----------    ----------
1123456-1-1   TEST1         DEV, APR, CAT, LFT, PKG

标签： sqlsql-serversql-server-2014string-aggregation

Answer 1

您可以使用 XML PATH 执行此操作

Select j.JobID, s.OrderNumber, 
(SELECT OperationID + ',' From Operations WHERE add_your_filter_here FOR XML PATH ('')) as Operations
From Jobs j
Inner Join Sales s on j.JobID = s.SalesJobID
Where j.JobID = '123456-1-1'