How to create a query to combine two or three rows for duplicated IDs

Question

I am trying to create a select statement to combine values from duplicated IDs on any specific columns.

My table is:

ID   Name
---------
01   A
01   B
02   C
03   D

How can I select to get values like: 01 A, B for ID: 01. Currently, when I use Select * from Tablename, it lists 01 ID for two rows. I like to combine it into one row only, Name should be combined with a comma for any duplicated rows.

New code:

select Name, ID = REPLACE
  ((select Surname AS [data()]
        FROM Mytable
        WHERE Name = d. Name
        ORDER BY Name FOR XML path('')), ' ', REQUIRED SEPERATOR)
FROM Mytable d
WHERE Name IS NOT NULL
GROUP BY Name

Thank you very much!

there is no group_concat-like operator in SQLServer, so you can not do that directly, but there are methods to achieve this: stackoverflow.com/questions/451415/… — shibormot
– shibormot, Commented Oct 18, 2017 at 16:39
Possible duplicate of Simulating group_concat MySQL function in Microsoft SQL Server 2005? — SQLChao
– SQLChao, Commented Oct 18, 2017 at 16:40
I tried that method as this above, but it says Incorrect syntacx near 'Seperator', Can anyone help? Thanks. — Jenny Tran
– Jenny Tran, Commented Oct 18, 2017 at 17:29

shibormot · Accepted Answer · 2017-10-19 18:21:15Z

1

SQL Fiddle

MS SQL Server 2014 Schema Setup:

create table t (id int not null, name nvarchar(max) not null)
insert t (id, name) values 
  (1, 'A'), 
  (1, 'B'),
  (1, 'C'), 
  (2, 'A'),
  (2, 'D'), 
  (3, 'A'),
  (3, 'F'), 
  (3, 'E')

Query 1:

    select id, REPLACE
      ((select name AS [data()]
            FROM t as t1
            WHERE t1.id = t0.id
            ORDER BY Name FOR XML path('')), ' ', ',')
    FROM t as t0
    GROUP BY id

Results:

| id |       |
|----|-------|
|  1 | A,B,C |
|  2 |   A,D |
|  3 | A,E,F |

UPDATE To deal with spaces already in names, we can replace them to underscores, before grouping, and replace back after:

  select id, replace(REPLACE
  ((select replace(name, ' ', '_') AS [data()]
        FROM t as t1
        WHERE t1.id = t0.id
        ORDER BY Name FOR XML path('')), ' ', ','), '_', ' ')
FROM t as t0
GROUP BY id

edited Oct 19, 2017 at 18:21

answered Oct 18, 2017 at 18:10

shibormot

1,6382 gold badges12 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jenny Tran Over a year ago

Only one small issue. If column name has two or more words like 'A B', or 'A B C', then every space was changed to a comma. How do I fix that? Thanks.

shibormot Over a year ago

you can replace spaces to, for example, underscores before concatenating, and replace back after

Jenny Tran Over a year ago

Perfect. Thanks very much!

Collectives™ on Stack Overflow

How to create a query to combine two or three rows for duplicated IDs

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related