Combine rows concatenating values

Question

I currently have a table:

id  | name  | type | type_name
----+-------+------+-----------
101 | John  | 1    | cat
101 | John  | 2    | dog
253 | Bill  | 2    | dog
376 | Rick  | 3    | giraffe
376 | Rick  | 2    | dog
440 | James | 2    | dog
440 | James | 1    | cat
440 | James | 3    | giraffe

I want to build a new view that uses the data from the table. If a 'name'/'id' has more than one type, then the types column below would combine them:

Considering there are only three possible types,

    combinedType: 
    1 = cat
    2 = dog
    3 = giraffe
    4 = cat and dog
    5 = cat and giraffe
    6 = dog and giraffe
    7 = cat, dog, and giraffe

id   | name  | combinedTypeID| newTypeName
-----+-------+---------------+------------------
101  | John  | 4             | cat and dog 
253  | Bill  | 2             | dog
376  | Rick  | 6             | dog and giraffe
440  | James | 7             | cat, dog, and giraffe

I've got brain fog on doing this without messy subqueries.

Thanks all!

Does this answer your? [Comma separated results in SQL](https://stackoverflow.com/q/18870326/2029983) — Thom A, Oct 11 '20 at 18:12

Somy · Accepted Answer · 2020-10-11T21:20:01.637

If you are ok to get rid of “and” in your string, then for SQL Server 2017 and later versions, you can use String_agg( ) function as below


Select 
         ID
       , name
       , case when count(1) > 1 then sum(type)+1 else sum(type) end as CombinedTypeId
       , String_agg(type_name,',') WITHIN GROUP (ORDER BY type) as newTypeName
from MyTable
  Group by 
         ID
       , name
  order by 1;

Here is a db fiddle link - https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=f361ab0050d94dce74958ad55a1afc93

score 1 · Answer 2 · answered Oct 11 '20 at 21:27

If you are not tied to those particular numbers and representations, I would suggest instead:

combinedType: 
1 = cat
2 = dog
3 = cat, dog
4 = giraffe
5 = cat, giraffe
6 = dog, giraffe
7 = cat, dog, giraffe

With this slight change the values are additive.

Then, the logic is pretty simple:

select t.id, t.name, sum(v.val) as type_id,
       string_agg(v.type_name, ',') within group (order by v.type_name)
from t join
     (values ('cat', 1), ('dog', 2), ('giraffe', 4)
     ) v(type_name, val)
     on t.type_name = v.type_name
group by t.id, t.name;

score 0 · Answer 3 · 2020-10-11T20:40:02.983

I think this query works well. But in the last case 'cat, dog, and giraffe', it returns 'cat and dog and giraffe'. And if you like you could have a table with your combinedtypes and join to the first CTE.

with NewTypesResult
as
(   select id, name, substring(newTypeName, 1, len(newTypeName) - 4) as newTypeName
    from
    (
        select distinct id, name, (select distinct type_name + ' and ' 
                            from myTable 
                            where id = tbl.id 
                            order by 1
                            for xml path('')) as newTypeName
        from dbo.myTable tbl
    ) as tbl
), FinalResult
as
(
    select id, name, newTypeName, case
                            when newTypeName = 'cat' then 1
                            when newTypeName like 'dog' then 2
                            when newTypeName like 'giraffe' then 3
                            when newTypeName like 'cat and dog' then 4
                            when newTypeName like 'cat and giraffe' then 5
                            when newTypeName like 'dog and giraffe' then 6
                            when newTypeName like 'cat and dog and giraffe' then 7
                            end as combinedTypeID
    from NewTypesResult
)
select id, name, newTypeName, combinedTypeID
from FinalResult

score 0 · Answer 4 · answered Oct 11 '20 at 21:24

The match condition can be met by aggregating the strings from the #Users table by ID and joining on the combinedType column. In the #TypesTable in the combinedType column the (substring) ' and ' is replaced by ', '. This causes there to be a double comma, i.e. ',, ' in the cases when there are 3 types per ID. So the outer REPLACE function changes',, ' back to ', '.

Sample data

drop table if exists #Users;
go
create table #Users(
ID              int not null,
[name]          nvarchar(50),
[type]          int,
[type_name]     nvarchar(50));
go

insert #Users(ID, [name], [type], [type_name]) values
(101, 'John', 1, 'cat'),
(101, 'John', 2, 'dog'),
(253, 'Bill', 2, 'dog'),
(376, 'Rick', 3, 'giraffe'),
(376, 'Rick', 2, 'dog'),
(440, 'James', 2, 'dog'),
(440, 'James', 1, 'cat'),
(440, 'James', 3, 'giraffe');

drop table if exists #TypesTable;
go
create table #TypesTable(
  [type]          int primary key,
  combinedType    nvarchar(100));
go

insert #TypesTable([type], combinedType) values
(1, 'cat'),
(2, 'dog'),
(3, 'giraffe'),
(4, 'cat and dog'),
(5, 'cat and giraffe'),
(6, 'dog and giraffe'),
(7, 'cat, dog, and giraffe');

Query

with 
types_cte(ID, [Name], combinedType) as (
    select ID, [Name], string_agg([type_name], ', ') 
                     within group (order by [type_name])
    from #Users
    group by ID, [Name]) 
select tc.id, tc.[name], t.[type] combinedTypeID, t.combinedType
from types_cte tc
     join #TypesTable t on tc.combinedType=replace(replace(t.combinedType, ' and ', ', '), ',, ', ', ')
order by 1;

Output

id  name    combinedTypeID  combinedType
101 John    4               cat and dog
253 Bill    2               dog
376 Rick    6               dog and giraffe
440 James   7               cat, dog, and giraffe

Combine rows concatenating values

4 Answers4