title: Return SQL Sub Query with a variable inside Select
body: <p>My Application is a cash register.  The following query takes a TransactionID and returns one or more rows of items sold in this single transaction i.e. for TransactionID = 28715:</p>
  
  <pre><code>DECLARE @SalesItems varchar(250);
  DECLARE @TransactionID int;
  SET @TransactionID = 28715;
  
  
  Select @SalesItems = coalesce(@SalesItems + ', ', '') + CAST(TransactionsPosLines.Description as varchar(250))
  From
    TransactionsPosLines
    where TransactionsPosLines.TransactionId = @TransactionID
  
  select @SalesItems
  </code></pre>
  
  <p>and returns:</p>
  
  <pre><code>"Widget A,  Widget B, Widget C" as a single string.
  </code></pre>
  
  <p>I also have an end of day transaction report which I want to append the string (Widget A etc...) onto the end of the transaction report.</p>
  
  <pre><code>        Select
            Transactions.TransactionId,
            Transactions.TransactionDate,
            Transactions.MoneyIn,
            Transactions.MoneyOut,
            Transactions.Description,
            PaymentMethods.PaymentMethodName,
            Transactions.TransactionRef,
            Membership.Username,
            Tills.Description As 'Till Name',
            Transactions.Reason,
      -- FOR THIS LAST COLUMN HERE I WANT TO SHOW THE OUTPUT OF THE QUERY ABOVE
  ====&gt;     SalesItems
          From
            Transactions Left Outer Join
            Tills
              On Transactions.TillId = Tills.TillId Inner Join
            PaymentMethods
              On Transactions.PaymentMethodId = PaymentMethods.PaymentMethodsID Inner Join
            Membership
              On Transactions.UserId = Membership.UserId Inner Join
          Where
            Transactions.TransactionDate &gt;= @DateStart And
            Transactions.TransactionDate &lt;= @DateEnd
  </code></pre>
  
  <p>So when I run this report I get something like this:</p>
  
  <pre><code>TransactionId   TransactionDate MoneyIn MoneyOut    Description PaymentMethodName   TransactionRef  Username    Till Name   Reason   SalesItems
  28715   31/08/2016 09:07    119.99  0   Sale - Card Card    24881   Chantal Till1  Null   Widget A, Widget B, Widget C
  </code></pre>