Suppose I have 3 tables (like shown below).

series_no table:

|   id  |  desc_seriesno  |
|:------|----------------:|
| 7040  |     AU1011      |
| 7041  |     AU1022      |
| 7042  |     AU1033      |
| 7043  |     AU1044      |
| 7044  |     AU1055      |
| 7045  |     AU1066      |

brand table:

|   id  |  desc_brand     |
|:------|----------------:|
| 1020  |     Audi        |
| 1021  |     Bentley     |
| 1022  |     Ford        |
| 1023  |     BMW         |
| 1024  |     Mazda       |
| 1025  |     Toyota      |

car_info table:

|   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   |

How can I group by or combine similar/identical string without changing them in concatenated string, but instead just overwrite same string?

This is my current query with SQL Server 2014:

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, B.desc_brand
ORDER BY SN.id ASC

Unfortunately it gave me an error since I cannot group by similar string this way.

I want it to be like this:

|  seriesid  |   brandid  |   desc_brand  |
|:-----------|------------|--------------:|
|    7040    |    1020    |     Audi      |
|    7041    |    1021    |     Bentley   |
|    7042    |    1022    |     Ford      |
|    7043    |    1023    |     BMW       |
|    7044    |    1024    |     Mazda     |
|    7045    |    1025    |     Toyota    |

instead of this (concatenated string):

|  seriesid  |   brandid  |       desc_brand      |
|:-----------|------------|----------------------:|
|    7040    |    1020    |     Audi, Audi, Audi  |
|    7041    |    1021    |     Bentley, Bentley  |
|    7042    |    1022    |     Ford, Ford, Ford  |
|    7043    |    1023    |     BMW               |
|    7044    |    1024    |     Mazda             |
|    7045    |    1025    |     Toyota, Toyota    |