title: Produce DISTINCT values in STRING_AGG
body: <p>I'm using the STRING_AGG function in SQL Server 2017.  I'd like to create the same effect as <code>COUNT(DISTINCT &lt;column&gt;)</code>.  I tried <code>STRING_AGG(DISTINCT &lt;column&gt;,',')</code> but that is not legal syntax.</p>
  
  <p>I'd like to know if there is a T-SQL work-around.  Here is my sample:</p>
  
  <pre><code>WITH Sitings 
    AS
    (
      SELECT * FROM (VALUES 
        (1, 'Florida', 'Orlando', 'bird'),
        (2, 'Florida', 'Orlando', 'dog'),
        (3, 'Arizona', 'Phoenix', 'bird'),
        (4, 'Arizona', 'Phoenix', 'dog'),
        (5, 'Arizona', 'Phoenix', 'bird'),
        (6, 'Arizona', 'Phoenix', 'bird'),
        (7, 'Arizona', 'Phoenix', 'bird'),
        (8, 'Arizona', 'Flagstaff', 'dog')
      ) F (ID, State, City, Siting)
    ) 
  SELECT State, City, COUNT(DISTINCT Siting) [# Of Types], STRING_AGG(Siting,',') Animals
  FROM Sitings 
  GROUP BY State, City
  </code></pre>
  
  <p>The above produces the following result:</p>
  
  <pre><code>+---------+-----------+--------------+-------------------------+
  |  State  |   City    | # Of Types   |         Animals         |
  +---------+-----------+--------------+-------------------------+
  | Arizona | Flagstaff |            1 | dog                     |
  | Florida | Orlando   |            2 | dog,bird                |
  | Arizona | Phoenix   |            2 | bird,bird,bird,dog,bird |
  +---------+-----------+--------------+-------------------------+
  </code></pre>
  
  <p>The output is exactly what I want, except I want the concatenated "Animals" listed for Phoenix Arizona to be DISTINCT, like this:</p>
  
  <pre><code>+---------+-----------+--------------+--------------------+
  |  State  |   City    | # Of Types   |      Animals       |
  +---------+-----------+--------------+--------------------+
  | Arizona | Flagstaff |            1 | dog                |
  | Florida | Orlando   |            2 | dog,bird           |
  | Arizona | Phoenix   |            2 | bird,dog           |
  +---------+-----------+--------------+--------------------+
  </code></pre>
  
  <p>Any ideas?</p>
  
  <p>When I use my real data set, which is much larger, I get an error about the "Animals" column exceeding 8000 characters.</p>
  
  <p>My question I think is the same as <a href="https://stackoverflow.com/questions/48985732/how-to-avoid-duplicates-in-the-string-agg-function">this one</a>, except my example is much simpler.</p>