Concatenate Values Based on Id

Concatenate values based on ID

You can not be sure about the order of the strings concatenated without an order by statement in the sub query. The .value('.', 'varchar(max)') part is there to handle the case where Label contains XML-unfriendly characters like &.

declare @T table(Response_ID int, Label varchar(50))
insert into @T values
(12147,          'It was not clear'),
(12458,          'Did not Undersstand'),
(12458,          'Was not resolved'),
(12458,          'Did not communicate'),
(12586,          'Spoke too fast'),
(12587,          'Too slow')

select T1.Response_ID,
       stuff((select ','+T2.Label
              from @T as T2
              where T1.Response_ID = T2.Response_ID
              for xml path(''), type).value('.', 'varchar(max)'), 1, 1, '') as Label
from @T as T1
group by T1.Response_ID

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

Concatenate values of records with same ID | Pandas

You can try groupby with cumsum() of lists like this:

Sample:

import pandas as pd
df = pd.DataFrame({'ID': {1: '1', 2: '1', 3: '1', 4: '2', 5: '2', 6: '2', 7: '3', 8: '3'}, 'Year': {1: 2020.0,  2: 2019.0,  3: 2018.0,  4: 2019.0,  5: 2018.0,  6: 2017.0,  7: 2020.0,  8: 2018.0}, 'Code': {1: 'a', 2: 'b', 3: 'c', 4: 'd', 5: 'e', 6: 'f', 7: 'g', 8: 'h'}})

Code:

df = df.sort_values(['ID', 'Year'], ascending=True)
df['Year'] = df['Year'].astype(int).astype(str)
df['Sequence'] = df.groupby(['ID'])['Year'].apply(lambda x: x.map(lambda x: [x]).cumsum()).str.join('x') + '_' + df['Code']
df = df.sort_values(['ID', 'Year'], ascending=[True, False])

R concatenate only unique values in rows by ID

How about

library(tidyverse)

Data %>% 
  group_by(Region, branch) %>% 
  summarise(
    across(
      everything(), 
      ~paste(as.list(unique(.)), collapse=","),
      .groups="drop"
    )
  ) 
# A tibble: 2 × 6
  Region branch ID1   ID2   ID3   ID4  
         
1      1 A      1     1,2   2     4,3  
2      2 B      2     2     2,3   2,4  

Edit

It also works without the as.list().

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 multiple rows into one based on id in sql server

Use STUFF and FOR XML PATH like this:

select id,
    stuff((
            select ',' + cast(MedId as varchar(30))
            from t t1
            where t1.id = t.id
            order by MedID
            for xml path('')
            ), 1, 1, '') MedID,
    stuff((
            select ',' + Name
            from t t1
            where t1.id = t.id
            order by MedID
            for xml path('')
            ), 1, 1, '') Name
from t
group by id;

Demo

How to concatenate object values with same id

You can use a combination of Array.reduce and Array.find.

If you find an existing item in your accumulator array, just update it's flat_no property, otherwise push it to the accumulator array.

let arr = [  {     uid: 1,     flat_no: 1  },  {     uid: 2,     flat_no: 2  },  {     uid: 1,     flat_no: 3  }]
arr = arr.reduce((arr, item) => {  const existing = arr.find(innerItem => innerItem.uid === item.uid)
  if (existing) {    existing.flat_no = Array.isArray(existing.flat_no)      ? existing.flat_no      : [existing.flat_no]
    existing.flat_no.push(item.flat_no)  } else {    arr.push(item)  }
  return arr}, [])
console.log(arr)

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

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

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]

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