CODEWITHSUNDEEP
sqlsql-server
Jenny Tran
Jenny Tran

Reputation: 251

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

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!

Upvotes: 0

Views: 56

Answers (1)

shibormot
shibormot

Reputation: 1638

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

Upvotes: 1

Related Questions