title: How can I return 2 rows of a field within the same row?
body: <p>Each row in the query below returns 1 unique row. <strong><em>However</em></strong>, if I were to add 2 more fields, the <code>job</code> and <code>suffix</code> field, I would get some rows with duplicate <code>item</code> numbers (this is expected).</p>
  
  <p>I want to actually return the <code>job</code> and <code>suffix</code> row information, but all in one field and in one row separated by commas. <a href="https://www.mssqltips.com/sqlservertip/2914/rolling-up-multiple-rows-into-a-single-row-and-column-for-sql-server-data/" rel="nofollow">See this link for a description of what I want to do</a>. I can't use this example though because I don't have the ability to write to a table, I can only make queries/reports/subreports with this. How can I do this?</p>
  
  <pre><code>SELECT c.item, 
         c.qty_ordered                                 AS qty_expected, 
         Sum(j.qty_released)                           AS sum_qty_released, 
         Sum(j.qty_complete)                           AS sum_qty_complete, 
         ( Sum(j.qty_complete) - c.qty_ordered )       AS expected_variance, 
         ( Sum(j.qty_complete) - Sum(j.qty_released) ) AS regular_variance, 
         j.whse 
  FROM   coitem AS c 
         LEFT OUTER JOIN job AS j 
                      ON c.item = j.item 
                         AND Dateadd(d, -6, c.due_date) = j.job_date 
  WHERE  j.job_date = @jobdate 
         AND j.item IN (SELECT j.item AS current_item 
                        FROM   job AS j 
                        WHERE  j.job_date = @jobdate 
                               AND j.item NOT LIKE 'Indirect' 
                               AND j.job NOT LIKE 'C%') 
  GROUP  BY c.item, 
            c.qty_ordered, 
            j.whse 
  ORDER  BY j.whse, 
            c.item 
  </code></pre>
  
  <p><strong>Update:</strong></p>
  
  <p>I attempted to use <code>FOR XML Path</code> to solve this, but I can't get it to work. I get this error: </p>
  
  <blockquote>
    <p><code>Incorrect syntax near the keyword 'FOR'. Incorrect syntax near the keyword 'GROUP'.</code></p>
  </blockquote>
  
  <p>The trickiest part of this is that the information I want returned in one row is the job <strong><em>and</em></strong> the suffix. So I need to concatenate these 2 fields first then aggregate the rows into one field. But I figured since the aggregation is more difficult, I should start with that. Hence, my attempt below only focuses on the aggregation part.</p>
  
  <p>This is my code:</p>
  
  <pre><code>DECLARE @jobdate datetime = '5/30/2016'
  
  SELECT
  
  
      c.item,
      c.qty_ordered AS qty_expected,
  
      j1.job,
      j1.suffix,
      Sum(j1.qty_released) AS sum_qty_released,
      Sum(j1.qty_complete) AS sum_qty_complete,
      (Sum(j1.qty_complete)-c.qty_ordered) AS expected_variance,
  
      (Sum(j1.qty_complete)-Sum(j1.qty_released)) AS regular_variance,
      j1.whse
      STUFF(
          SELECT DISTINCT '' + j2.job
          FROM job AS j2 
          WHERE j1.job = j2.job AND j1.suffix = j2.suffix 
          FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)')
          ,1,0,'') data 
  
  FROM
  
      coitem AS c
      LEFT OUTER JOIN job AS j1 ON c.item = j1.item AND DATEADD(d, -6, c.due_date) = j1.job_date
  
  WHERE
  
  
      j1.job_date = @jobdate AND
      j1.item IN
  
          (SELECT
  
              j.item AS current_item
  
          FROM
  
              job AS j
  
          WHERE
  
              j.job_date = @jobdate AND
              j.item NOT LIKE 'Indirect' AND
              j.job NOT LIKE 'C%'
          )
  
  GROUP BY
  
      c.item,
      c.qty_ordered,
      j1.whse,
      j1.job,
      j1.suffix
  
  ORDER BY
  
      j1.whse,
      c.item
  </code></pre>