How to Concatenate Values with Same Id in SQL

How to concatenate many rows with same id in sql?

In SQL-Server you can do it in the following:

QUERY

SELECT id, displayname = 
    STUFF((SELECT DISTINCT ', ' + displayname
           FROM #t b 
           WHERE b.id = a.id 
          FOR XML PATH('')), 1, 2, '')
FROM #t a
GROUP BY id

TEST DATA

create table #t 
(
id int,
displayname nvarchar(max)
)

insert into #t values    
 (1 ,'Editor')
,(1 ,'Reviewer')
,(7 ,'EIC')
,(7 ,'Editor')
,(7 ,'Reviewer')
,(7 ,'Editor')
,(19,'EIC')
,(19,'Editor')
,(19,'Reviewer')

OUTPUT

id  displayname
1   Editor, Reviewer
7   Editor, EIC, Reviewer
19  Editor, EIC, Reviewer

How to concatenate text from multiple rows into a single text string in SQL Server

If you are on SQL Server 2017 or Azure, see Mathieu Renda answer.

I had a similar issue when I was trying to join two tables with one-to-many relationships. In SQL 2005 I found that XML PATH method can handle the concatenation of the rows very easily.

If there is a table called STUDENTS

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

Result I expected was:

SubjectID       StudentName
----------      -------------
1               Mary, John, Sam
2               Alaina, Edward

I used the following T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

You can do the same thing in a more compact way if you can concat the commas at the beginning and use substring to skip the first one so you don't need to do a sub-query:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

Concatenate fields of rows with the same ID in MySQL

You need to add group by:

SELECT m.id, GROUP_CONCAT(distinct(g.loc)) 
FROM mutations m JOIN
    genes g
    ON m.id = g.id
GROUP BY m.id;

Along the way, you should learn a couple other things:

• Use explicit join syntax. A simple rule: never use commas in the from clause.
• Use table aliases (the m and g). They make the query easier to write and to read.

How to concatenate values with same id in sql

These both links help u to do this task :

http://jahaines.blogspot.com/2009/07/concatenating-column-values-part-2.html

http://jahaines.blogspot.com/2009/06/concatenating-column-values-part-1.html

Combine two rows in a single row with the same ID name

A simple aggregation should do the trick

Example

Select ID
      ,ID1 = max(ID1)
      ,ID2 = max(ID2)
      ,ID3 = max(ID3)
 From  YourTable
 Group By ID

MYSQL how to merge rows with same field id into a single row

GROUP_CONCAT supports DISTINCT and SEPARATOR``

CREATE TABLE table1 (
  `rowid` VARCHAR(139),
  `title` VARCHAR(139),
  `author_f_name` VARCHAR(139),
  `author_m_name` VARCHAR(139),
  `author_l_name` VARCHAR(139),
  `coauthor_first_name` VARCHAR(139),
  `coauthor_middle_name` VARCHAR(139),
  `coauthor_last_name` VARCHAR(139)
);

INSERT INTO table1
  (`rowid`, `title`, `author_f_name`, `author_m_name`, `author_l_name`, `coauthor_first_name`, `coauthor_middle_name`, `coauthor_last_name`)
VALUES
  ('1.',   'Blog Title.',       'Roy',                  NULL,                   'Thomas.',               'Joe',                   'Shann',               'Mathews'),
  ('1.',   'Blog Title.',       'Thomas',                  'NULL',              'Edison',            'Kunal',               NULL,                       'Shar');

SELECT 
`rowid`
, GROUP_CONCAT(DISTINCT `title`  SEPARATOR ' |||') tilte
, GROUP_CONCAT(DISTINCT `author_f_name` SEPARATOR ' |||') author_f_name
, GROUP_CONCAT(DISTINCT `author_m_name` SEPARATOR ' |||') author_m_name
, GROUP_CONCAT(DISTINCT `author_l_name` SEPARATOR ' |||') author_l_name
, GROUP_CONCAT(DISTINCT `coauthor_first_name` SEPARATOR ' |||') coauthor_first_name
, GROUP_CONCAT(DISTINCT `coauthor_middle_name` SEPARATOR ' |||') coauthor_middle_name
, GROUP_CONCAT(DISTINCT `coauthor_last_name` SEPARATOR ' |||') coauthor_last_name
FROM table1
GROUP BY `rowid`

rowid | tilte       | author_f_name | author_m_name | author_l_name     | coauthor_first_name | coauthor_middle_name | coauthor_last_name
:---- | :---------- | :------------ | :------------ | :---------------- | :------------------ | :------------------- | :-----------------
1.    | Blog Title. | Roy |||Thomas | NULL          | Edison |||Thomas. | Joe |||Kunal        | Shann                | Mathews |||Shar   

db<>fiddle here

SELECT 
`rowid`
, GROUP_CONCAT(DISTINCT `title`  SEPARATOR ' |||') tilte
, GROUP_CONCAT(DISTINCT CONCAT(`author_f_name`,' ',COALESCE(`author_m_name`,''),' ',`author_l_name`) SEPARATOR ' |||') author_full_name
, GROUP_CONCAT(DISTINCT CONCAT(`coauthor_first_name`,' ',COALESCE(`coauthor_middle_name`,''),' ',`coauthor_last_name`) SEPARATOR ' |||') coauthor_full_name
FROM table1
GROUP BY `rowid`

rowid | tilte       | author_full_name                   | coauthor_full_name              
:---- | :---------- | :--------------------------------- | :-------------------------------
1.    | Blog Title. | Roy  Thomas. |||Thomas NULL Edison | Joe Shann Mathews |||Kunal  Shar

db<>fiddle here

---

Related Topics