title: concatenate string inside a select statement
body: <p>Am new to sql server and would like your help. I have two tables <code>ProductCategory</code>- which has two fields <code>CategoryID and CategoryName</code>- and <code>Products</code> tables which has five fields <code>ProductID,ProductName,CategoryID,Price</code> and <code>Status</code>. What query should i use that will display the <code>ProductID,ProductName,Price,Status,ProductCategory</code> (Which Should be the <code>CategoryID,CategoryName</code> concatenated from the <code>CategoryTable</code>) </p>
  
  <p>I have tried </p>
  
  <pre><code>SELECT ProductID,
         ProductName,
  (
      SELECT CONCAT(CategoryID, CategoryName)
      FROM Category
  ) AS ProductCategory
  FROM Items;
  </code></pre>