title: Comma separated values from SQL, getting distinct values and their count
body: <p>I have this issue where i have a <code>column</code> in <code>SQL table</code>. the values are in <code>comma separated</code> format.</p>
  
  <p>ex:</p>
  
  <pre><code> 1)   facebook,google,just giving, news letter, in-store
   2)   facebook,google,just giving
   3)   just giving
   4)   facebook
   5)   google
   6)   in-store,email
   7)   email,facebook
  </code></pre>
  
  <p>Now i want to query that table and get this values in a list where list contains distinct values and their count. </p>
  
  <p>ex:</p>
  
  <pre><code>1) facebook - 10 
  2) email - 20
  3) in-store -5
  and so on....
  </code></pre>
  
  <p>is there anyway to achieve this using LINQ?</p>
  
  <p>NOTE: i can work on LINQ operators on LIST variable because my datasource which is '<code>entries'</code> does not support LINQ( built in .NET 2.0)</p>
  
  <pre><code>        var results = new List&lt;string&gt;();
          var builder = new StringBuilder();
  
          foreach (var entry in entries.Cast&lt;MWCompetitionsEntry&gt;().Where(entry =&gt; entry.HowFound != null))
          {
                builder.Append(entry.HowFound).Append(",");
          }
  
          results.Add(builder.ToString());
  </code></pre>
  
  <h1>RESULTS i am getting</h1>
  
  <pre><code>facebook,email,in-store,google,in-store,newsletter,facebook,email,facebok
  </code></pre>
  
  <h1>EDITS:</h1>
  
  <p>Now, I can work on <code>results</code> variable. i dont know how to get the unique values from that variable and their count.</p>
  
  <p>please help</p>
  
  <h1>ANSWER</h1>
  
  <pre><code>var values = new List&lt;string&gt;();
              var builder = new StringBuilder();
              var howFoundValues = new List&lt;string&gt;{"Email", "Facebook", "Twitter", "Leaflet", "Just Giving", "In-Store", "Other"};
  
              foreach (var entry in entries.Cast&lt;MWCompetitionsEntry&gt;().Where(entry =&gt; entry.HowFound != null))
              {
                    builder.Append(entry.HowFound).Append(",");
              }
  
              values.Add(builder.ToString());
              var result1 = values.SelectMany(l =&gt; l.Split(','))
                    .Where(howFoundValues.Contains) //will disregard any other items
                    .GroupBy(e =&gt; e)
                    .Select(e =&gt; String.Format("{0} - {1}",e.Key, e.Count()))
                    .ToList();
  </code></pre>