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: https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — shibormot, Oct 18 '17 at 16:39
Possible duplicate of [Simulating group\_concat MySQL function in Microsoft SQL Server 2005?](https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005) — SQLChao, Oct 18 '17 at 16:40
I tried that method as this above, but it says Incorrect syntacx near 'Seperator', Can anyone help? Thanks. — Jenny Tran, Oct 18 '17 at 17:29
@jenny try `','` instead of `REQUIRED SEPARATOR`, it is just placeholder for actual separator character — shibormot, Oct 18 '17 at 17:52
No more error. But the result is not right. ID column now shows all names, separated with comma, like A, B, A, B, A, B. It is supposed to show 01. Thanks. — Jenny Tran, Oct 18 '17 at 17:57

shibormot · Accepted Answer · 2017-10-19T18:21:15.243

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 '17 at 18:21

answered Oct 18 '17 at 18:10

shibormot

1,638
2
12
23

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. – Jenny Tran Oct 19 '17 at 12:16
you can replace spaces to, for example, underscores before concatenating, and replace back after – shibormot Oct 19 '17 at 18:04
Perfect. Thanks very much! – Jenny Tran Oct 19 '17 at 18:37

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

1 Answers1