title: Group By same or similar string sql
body: <p><strong>1)</strong> Suppose i have a table like this:-</p>
  
  <pre><code>| id    |  color_code  |      fruit      |
  |:------|--------------|----------------:|
  | 1     |  000001      |      apple      |     
  | 2     |  000001      |      apple      |    
  | 3     |  000001      |      apple      |      
  | 4     |  000002      |      lemon      |         
  | 5     |  000002      |      lemon      |       
  | 6     |  000003      |      grapes     |
  | 7     |  000003      |      grapes     | 
  </code></pre>
  
  <p>How can i <strong>group by</strong> the fruit column according to the color_code column in sql server?</p>
  
  <p>like this i suppose:-</p>
  
  <pre><code>| id    |  color_code  |      fruit      |   group_concat(id)  |
  |:------|--------------|-----------------|---------------------|
  | 1     |  000001      |      apple      |        1,2,3        |
  | 4     |  000002      |      lemon      |        2,5          |
  | 6     |  000003      |      grapes     |        6,7          |
  </code></pre>
  
  <p><strong>2)</strong> What if i have 3 tables (like shown below) which require join, how can i achieve this?</p>
  
  <p><strong>series_no</strong> table:</p>
  
  <pre><code>|   id  |  desc_seriesno  |
  |:------|----------------:|
  | 7040  |     AU1011      |
  | 7041  |     AU1022      |
  | 7042  |     AU1033      |
  | 7043  |     AU1044      |
  | 7044  |     AU1055      |
  | 7045  |     AU1066      |
  </code></pre>
  
  <p><strong>brand</strong> table:</p>
  
  <pre><code>|   id  |  desc_brand     |
  |:------|----------------:|
  | 1020  |     Audi        |
  | 1021  |     Bentley     |
  | 1022  |     Ford        |
  | 1023  |     BMW         |
  | 1024  |     Mazda       |
  | 1025  |     Toyota      |
  </code></pre>
  
  <p><strong>car_info</strong> table:</p>
  
  <pre><code>|   seriesno_id  |  brand_id  |  color  |
  |:---------------|------------|--------:|
  |     7040       |    1020    | white   |
  |     7040       |    1020    | black   |
  |     7040       |    1020    | pink    |
  |     7041       |    1021    | yellow  |
  |     7041       |    1021    | brown   |
  |     7042       |    1022    | purple  |
  |     7042       |    1022    | black   |
  |     7042       |    1022    | green   |
  |     7043       |    1023    | blue    |
  |     7044       |    1024    | red     |
  |     7045       |    1025    | maroon  |
  |     7045       |    1025    | white   |    
  </code></pre>
  
  <p>this is my current query with sql server 2014:-</p>
  
  <pre><code>SELECT SN.id AS seriesid, B.id AS brandid, B.desc_brand
  FROM [db1].[dbo].[series_no] SN
    LEFT JOIN [db1].[dbo].[car_info] CI
    ON CI.seriesno_id = SN.id
    RIGHT JOIN [db1].[dbo].[brand] B
    ON B.id = CI.brand_id
  GROUP BY SN.id, B.id
  ORDER BY SN.id ASC
  </code></pre>
  
  <p>but unfortunately it gave me an error since i cannot group by similar string this way.</p>
  
  <p>i want it to be like this:-</p>
  
  <pre><code>|  seriesid  |   brandid  |   desc_brand  | count |
  |:-----------|------------|---------------|-------|
  |    7040    |    1020    |     Audi      |   3   |
  |    7041    |    1021    |     Bentley   |   2   |
  |    7042    |    1022    |     Ford      |   3   |
  |    7043    |    1023    |     BMW       |   1   |
  |    7044    |    1024    |     Mazda     |   1   |
  |    7045    |    1025    |     Toyota    |   2   |
  </code></pre>