Nested Loop inside a SQL Statement

Question

I have been trying to figure out if there is a way to have a Loop inside of a SQL Statement.

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'

this is what we have

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

Trying to do something like this.

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

Answer 1

You can do this with 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'