title: SQL count column and list values counted
body: <p>I am trying to create a query that will output a count of a column and then list the names it counted:</p>
  
  <p>Table 1: license_granted  </p>
  
  <pre><code>users      checkout  
  Bob        2014-11-18 01:00:00  
  Steve      2014-11-18 01:00:00
  Bob        2014-11-18 01:30:00
  Chris      2014-11-18 01:30:00
  </code></pre>
  
  <p>I am able to do my count and get this output: </p>
  
  <pre><code>checkout_time            ctUsers  
  2014-11-18 01:00:00         2
  2014-11-18 01:30:00         2
  
  SELECT COUNT(DISTINCT users) AS ctUsers, checkout
  FROM license_granted
  GROUP BY checkout 
  </code></pre>
  
  <p>What I would like to get is:</p>
  
  <pre><code>checkout_time            ctUsers     userlist
  2014-11-18 01:00:00         2        Bob,Steve
  2014-11-18 01:30:00         2        Bob,Chris
  </code></pre>
  
  <p>Can this be done in the query?</p>
  
  <p>Thanks</p>
  
  <p>UPDATE 11/20 Okay I dug a little further and found a solution that gives me the checkout time and the userlist.  Now I am trying to figure out how to count the elements in the userlist:</p>
  
  <pre><code>SELECT a.ctDate, SUBSTRING(d.users,1, LEN(d.users) - 1) usersList, count(d.users) AS ctUsers
  FROM  (
     SELECT DISTINCT convert(varchar(10), deniedTime, 126) AS ctDate
      FROM hyperworks_checkouts
      ) a
      CROSS APPLY
      (
          SELECT DISTINCT [username] + ', ' 
          FROM hyperworks_checkouts AS B 
          WHERE A.ctdate = convert(varchar(10), deniedTime, 126)
          FOR XML PATH('')
      ) d (users)
  </code></pre>
  
  <p>The output now looks like this:</p>
  
  <pre><code>ctDate        usersList
  2014-01-15    Bob
  2014-01-16    Steve,Bob
  2014-01-17    Mike,Chris,Jerry
  </code></pre>
  
  <p>If I try adding COUNT(d.users) in the Select I get a count of 1 each time because there is one list.  How do I count the users in d.users?</p>
  
  <p>Thanks</p>