title: SQL - summarize all Product Types from a list of Authorized
body: <p>I'm trying to figure out how to display the TYPES of orders each client can place with us. We have the following tables CLIENTS, CLIENT_SERVICE_TYPE, SERVICE_TYPES, PRODUCT_TYPE</p>
  
  <p>We have many service types which can be easily associated with Fruits and Vegetables. </p>
  
  <blockquote>
    <p>CLIENTS - Lists all our clients<br>
     CLIENT_SERVICE_TYPE - lists all of the service types (Apples, Pears, Bananas, Squash, Chicken) they are authorized to place.<br>
     SERVICE_TYPES - lists the hundreds and hundreds of fruits and vegetables we offer (Of which, each client can only order a select few from.<br>
     PRODUCT_TYPE lists the 5 categories we offer (IE Fruits, Vegetables, Spices, Dairy, Meat)</p>
  </blockquote>
  
  <p>I want to be able to create a summary report that will display certain Clients like this</p>
  
  <p>Company Name | Sales Rep  | <strong>Product Types</strong><br>
  Company ABC  | Jane Smith | <strong>Fruits</strong><br>
  Company XYZ  | Joe Aardvark | <strong>Vegetables, Fruits</strong><br>
  Company 123  | Amy Addams | <strong>Meat, Vegetables, Fruits</strong>  </p>
  
  <p>In the above example, Company ABC has 15 authorized Fruits (Bananas, Apples, Oranges, etc) but its summarized as 1 on one line <strong>Fruits</strong>.  Company XYZ has the same 15 authorized fruits and 1 carrot.  I want it to show up (order doesn't matter) as  <strong>Vegetables, Fruits</strong> on the same line.  Company 123 is authorized for only 4 services (Chicken, Bananas, Peas and Squash).  <strong>Meat, Vegetables, Fruits</strong>  </p>
  
  <p>I know this is probably simple to find existing tutorials on, but my terminology is obviously not up to par.  This is as far as I got and as you can probably guess, it's showing up multiple lines for the X amount of service types they are approved for. </p>
  
  <pre><code>SELECT
      company [Client Name]
      ,salesperson [Sales Rep]
      --,pt.productTypeName [List of approved Product Types]
      FROM CLIENT cl (NOLOCK)
          LEFT JOIN
      CLIENT_SERVICE_TYPE cst (NOLOCK) = cl.client_key = cst.client_key
          LEFT JOIN
      SERVICE_TYPE st (NOLOCK) = cst.service_key = st.service_key
          LEFT JOIN
      PRODUCT_TYPE pt (NOLOCK) = cst.productTypeId = pt.productTypeId
  WHERE cl.client_program_id in (1,2,3)
      and status = 160
  ORDER BY company
  </code></pre>