0

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
mrmcg
  • 173
  • 6
  • 17
  • Why loop??? SQL is a set based language. You don't loop. Try to look up `STRING_AGG()` if you are using SQL Server. Otherwise tag your question properly with appropriate tag. – Eric Apr 15 '19 at 16:23
  • Also there are tons of example about this on the web. Have you done any research at all??? – Eric Apr 15 '19 at 16:25
  • @Eric yes I have. – mrmcg Apr 15 '19 at 16:30
  • There are many ways to do this, some of it depends on the database engine you are using (you didn't specify). You could do with a CTE, CURSOR, FOR XML PATH. See this article for may ways of accomplishing your goal. https://www.red-gate.com/simple-talk/sql/t-sql-programming/concatenating-row-values-in-transact-sql/ – Kevin Apr 15 '19 at 16:30
  • @mrmcg If you had, you wouldn't have needed to ask this question. There are more than enough examples in the web. I don't even see your attempt to get the query you want. – Eric Apr 15 '19 at 16:33

1 Answers1

0

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'
FizzBuzz
  • 683
  • 3
  • 8