Group By Identical string without converting to concatenated string

Question

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

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

No. I would prefer not to have it as concatenated string because desc_brand basically holding the same string. Is it possible to do so? (without converting to concatenated string) — user3657273, Dec 15 '16 at 07:41

ahmed abdelqader · Answer 1 · 2016-12-15T08:04:54.070

Simply You need avoid the duplicates so use one of the next two approaches:

Group by

select col1, col2 .. 
from table1 a inner join table2 b
on ..
group by col1, col2 ..

Distinct

 select distinct col1, col2 .. 
    from table1 a inner join table2 b
    on ..

Demo:-

Create database TestDB
go
use TestDB
go
Create table series_no (id int ,desc_seriesno varchar(20) )
go
insert into series_no values (7040, 'AU1011'),
                        (7041, 'AU1022'),
                        (7042, 'AU1033'),
                        (7043, 'AU1044'),
                        (7044, 'AU1055'),
                        (7045, 'AU1066')
go
Create table brand (id int ,desc_brand varchar(20) )
go
insert into brand values (1020, 'Audi'),
                        (1021, 'Bentley'),
                        (1022, 'Ford'),
                        (1023, 'BMW'),
                        (1024, 'Mazda'),
                        (1025, 'Toyota')

Create table car_info (seriesno_id int ,brand_id varchar(20), color varchar (20) )
go
insert into car_info values(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')
go

select a.seriesno_id as seriesnoid, a.brand_id as brandid,b.desc_brand
from car_info a inner join brand b
on brand_id = id
group by a.seriesno_id , a.brand_id ,b.desc_brand

/*
-- Or
select distinct a.seriesno_id as seriesnoid, a.brand_id as brandid,b.desc_brand
from car_info a inner join brand b
on brand_id = id
*/

Result:-

it woks! If i run this code as it is, it will give me this error "The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator" since my desc_brand declared as ntext. So i changed my query according to this solution here [link](http://stackoverflow.com/questions/14979413/the-text-ntext-and-image-data-types-cannot-be-compared-or-sorted-except-whe) instead of changing the var type in the database. — user3657273, Dec 19 '16 at 08:16
so there is lack of information form the beginning while asking for solution your issue. it is supposed to provide us with the structure of your tables. , nice to give u a clue for the solution. — ahmed abdelqader, Dec 19 '16 at 08:30

Group By Identical string without converting to concatenated string

1 Answers1