How to concatenate all strings from a certain column for each group
If MS SQL 2005 or higher.
declare @t table([name] varchar(max), mark int)
insert @t values ('ABC', 10), ('DEF', 10), ('GHI', 10),
('JKL', 20), ('MNO', 20), ('PQR', 30)
select t.mark, COUNT(*) [count]
,STUFF((
select ',' + [name]
from @t t1
where t1.mark = t.mark
for xml path(''), type
).value('.', 'varchar(max)'), 1, 1, '') [values]
from @t t
group by t.mark
Output:
mark count values
----------- ----------- --------------
10 3 ABC,DEF,GHI
20 2 JKL,MNO
30 1 PQR
Concatenate strings from several rows using Pandas groupby
You can groupby the 'name'
and 'month'
columns, then call transform
which will return data aligned to the original df and apply a lambda where we join
the text entries:
In [119]:
df['text'] = df[['name','text','month']].groupby(['name','month'])['text'].transform(lambda x: ','.join(x))
df[['name','text','month']].drop_duplicates()
Out[119]:
name text month
0 name1 hej,du 11
2 name1 aj,oj 12
4 name2 fin,katt 11
6 name2 mycket,lite 12
I sub the original df by passing a list of the columns of interest df[['name','text','month']]
here and then call drop_duplicates
EDIT actually I can just call apply
and then reset_index
:
In [124]:
df.groupby(['name','month'])['text'].apply(lambda x: ','.join(x)).reset_index()
Out[124]:
name month text
0 name1 11 hej,du
1 name1 12 aj,oj
2 name2 11 fin,katt
3 name2 12 mycket,lite
update
the lambda
is unnecessary here:
In[38]:
df.groupby(['name','month'])['text'].apply(','.join).reset_index()
Out[38]:
name month text
0 name1 11 du
1 name1 12 aj,oj
2 name2 11 fin,katt
3 name2 12 mycket,lite
How to use GROUP BY to concatenate strings in SQL Server?
No CURSOR, WHILE loop, or User-Defined Function needed.
Just need to be creative with FOR XML and PATH.
[Note: This solution only works on SQL 2005 and later. Original question didn't specify the version in use.]
CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
Concatenate strings by group with dplyr
You could simply do
data %>%
group_by(foo) %>%
mutate(bars_by_foo = paste0(bar, collapse = ""))
Without any helper functions
Concatenate multiple result rows of one column into one, group by another column
Simpler with the aggregate function string_agg()
(Postgres 9.0 or later):
SELECT movie, string_agg(actor, ', ') AS actor_list
FROM tbl
GROUP BY 1;
The 1
in GROUP BY 1
is a positional reference and a shortcut for GROUP BY movie
in this case.
string_agg()
expects data type text
as input. Other types need to be cast explicitly (actor::text
) - unless an implicit cast to text
is defined - which is the case for all other string types (varchar
, character
, name
, ...) and some other types.
As isapir commented, you can add an ORDER BY
clause in the aggregate call to get a sorted list - should you need that. Like:
SELECT movie, string_agg(actor, ', ' ORDER BY actor) AS actor_list
FROM tbl
GROUP BY 1;
But it's typically faster to sort rows in a subquery. See:
- Create array in SELECT
Concatenate values that are grouped by a column
You have nothing linking your inner and outer references to [Table]
, and you also need to make the outer reference distinct. Finally you need to either have no column name within your subquery, or it needs to be [text()]
SELECT [Code]
,[Ref]
,STUFF((SELECT DISTINCT [Value] AS [text()]
FROM [Table] AS T2
WHERE T1.Code = T2.Code -- LINK HERE
AND T2.Ref = T2.Ref -- AND HERE
FOR XML PATH ('')
),1, 1,'') AS [Values]
FROM [Table] AS T1
GROUP BY T1.Code, T1.Ref; -- GROUP BY HERE
As an aside, you do not need to use STUFF
as you have no delimiter, STUFF
is typically used to remove the chosen delimiter from the start of the string. So when you have a string like ,value1,value2,value3
, STUFF(string, 1, 1, '')
will replace the first character with ''
leaving you with value1,value2,value3
.
You should also use the value
xquery method to ensure you are not tripped up by special characters, if you don't and you try an concatenate ">>"
and "<<"
you would not end up with ">><<"
as you might want, you would get ">><<"
, so a better query would be:
SELECT t1.Code,
t1.Ref,
[Values] = (SELECT DISTINCT [text()] = [Value]
FROM [Table] AS t2
WHERE T1.Code = T2.Code
AND T2.Ref = T2.Ref
FOR XML PATH (''), TYPE
).value('.', 'NVARCHAR(MAX)')
FROM [Table] AS T1
GROUP BY t1.Code, t1.Ref;
ADDENDUM
Based on the latest edit to the question it appears as though your Value
column is coming from another table, linked to the first table by Code
. If anything this makes your query simpler. You don't need the JOIN
, but you still need to make sure that there is an expression to link the outer table to the inner table your subquery. I am assuming that the rows are unique in the first table, so you probably don't need the group by either:
SELECT t1.Code,
t1.Ref,
[Values] = (SELECT DISTINCT [text()] = t2.[Value]
FROM [Table2] AS t2
WHERE T1.Code = T2.Code
FOR XML PATH (''), TYPE
).value('.', 'NVARCHAR(MAX)')
FROM [Table] AS T1;
WORKING EXAMPLE
CREATE TABLE #Table1 (Code CHAR(2), Ref VARCHAR(10));
INSERT #Table1 VALUES ('A1', 'Car'), ('B2', 'Truck'), ('C3', 'Van');
CREATE TABLE #Table2 (Code CHAR(2), Value VARCHAR(2));
INSERT #Table2
VALUES ('A1', 'A'), ('A1', '-'), ('A1', 'B'),
('B2', 'CC'), ('B2', 'D'), ('B2', '-'),
('C3', 'F'), ('C3', '-'), ('C3', 'G');
SELECT t1.Code,
t1.Ref,
[Values] = (SELECT DISTINCT [text()] = t2.[Value]
FROM #Table2 AS t2
WHERE T1.Code = T2.Code
FOR XML PATH (''), TYPE
).value('.', 'NVARCHAR(MAX)')
FROM #Table1 AS T1;
Can I concatenate multiple MySQL rows into one field?
You can use GROUP_CONCAT
:
SELECT person_id,
GROUP_CONCAT(hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Ludwig stated in his comment, you can add the DISTINCT
operator to avoid duplicates:
SELECT person_id,
GROUP_CONCAT(DISTINCT hobbies SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Jan stated in their comment, you can also sort the values before imploding it using ORDER BY
:
SELECT person_id,
GROUP_CONCAT(hobbies ORDER BY hobbies ASC SEPARATOR ', ')
FROM peoples_hobbies
GROUP BY person_id;
As Dag stated in his comment, there is a 1024 byte limit on the result. To solve this, run this query before your query:
SET group_concat_max_len = 2048;
Of course, you can change 2048
according to your needs. To calculate and assign the value:
SET group_concat_max_len = CAST(
(SELECT SUM(LENGTH(hobbies)) + COUNT(*) * LENGTH(', ')
FROM peoples_hobbies
GROUP BY person_id) AS UNSIGNED);
How to use GROUP BY to concatenate strings in MySQL?
SELECT id, GROUP_CONCAT(name SEPARATOR ' ') FROM table GROUP BY id;
https://dev.mysql.com/doc/refman/8.0/en/aggregate-functions.html#function_group-concat
From the link above, GROUP_CONCAT
: This function returns a string result with the concatenated non-NULL values from a group. It returns NULL if there are no non-NULL values.
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
Related Topics
Oracle Db: How to Write Query Ignoring Case
How to Change Date Format in Hive
Compare 3 Consecutive Rows in a Table
Generating Rows Based on Column Value
Setting Up Foreign Key with Different Datatype
Percentiles from Histogram Data
Not Deferrable Versus Deferrable Initially Immediate
Possible to Restore a Backup of SQL Server 2014 on SQL Server 2012
How to Create a "Unique" Constraint on a Boolean MySQL Column
SQL Server 2008 Express Concat() Doesn't Exist
How to Find the Total Number of Used Days in a Month
Select Second Most Minimum Value in Oracle