title: SQL query for "concatenate on join"
body: <p>I'm using a <strong>Sybase ASE</strong> database.<br>
  I have two tables that look like:</p>
  
  <p>Table <code>Shops</code>: </p>
  
  <pre><code>---------------------
  | ShopName | ShopID |
  ---------------------
  | Sweetie  | 1      |
  | Candie   | 2      |
  | Sugarie  | 3      |
  ---------------------
  </code></pre>
  
  <p>Table <code>Sweets</code>:</p>
  
  <pre><code>----------------------
  | SweetName | ShopID |
  ----------------------
  | lolly     | 1      |
  | redlolly  | 1      |
  | greenloly | 1      |
  | taffy     | 2      |
  | redtaffy  | 2      |
  | bluetaffy | 2      |
  | choco     | 3      |
  | mintchoco | 3      |
  | milkchoco | 3      |
  | gummybees | 3      |
  ----------------------
  </code></pre>
  
  <p>I want to write a query that would generate a result that looks like:</p>
  
  <pre><code>-----------------------------------------------------
  | ShopName | Sweets                                 |
  -----------------------------------------------------
  | Sweetie  | lolly, redlolly, greenlolly            |
  | Candie   | taffy, redtaffy, bluetaffy             |
  | Sugarie  | choco, mintchoco, milkchoco, gummybees |
  -----------------------------------------------------
  </code></pre>
  
  <p>How should I go about doing that? I need this for a Sybase ASE database. I tried the <code>LIST()</code> function, but I'm getting an error on that. I checked its documentation, and turns out, this function is not available in the ASE Edition.</p>
  
  <p>This probably means that there will be some "dynamic sql" involved (I have very little idea what that means). Can anyone help?</p>
  
  <p>I could want <code>ShopId</code> instead of <code>ShopName</code> in the results table... I don't know for sure yet. I guess that won't be much of a difference. Also, trailing commas in <code>Sweets</code> column of results is not an issue. All I want is a non-whitespace separator.</p>