Concatenate Grouped Rows

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

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

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 concatenate rows grouped by columns in R Data frame?

One option is after grouping by 'ID', paste the 'city' elements together in mutate for creating a new column

library(dplyr)
df1 %>% 
   group_by(ID) %>% 
   mutate(newcol = paste(city, collapse= ' '))

Group Concatenate Strings (Rows) in BigQuery

Do each unnest separately:
Does aggregation work?

SELECT STRING_AGG(item.abc, ',')
       uniqueid, variable2, cust.variable1, purch.variable2
FROM mydata.order LEFT JOIN
     UNNEST(purchases) as purch
     ON true LEFT JOIN
     UNNEST(codes_abs) as cod
     ON true LEFT JOIN
     UNNEST(cod.try_items) as item
     ON true
GROUP BY uniqueid, variable2, cust.variable1, purch.variable2;

Concatenate and group multiple rows in Oracle

Consider using LISTAGG function in case you're on 11g:

select grp, listagg(name,',') within group( order by name ) 
  from name_table group by grp

sqlFiddle

upd: In case you're not, consider using analytics:

select grp,
    ltrim(max(sys_connect_by_path
       (name, ',' )), ',')
        scbp
  from (select name, grp,
            row_number() over
           (partition by grp
            order by name) rn
         from tab
          )
start with rn = 1
connect by prior rn = rn-1
and prior grp = grp
  group by grp
  order by grp

sqlFiddle

Concatenate across columns and rows using group and listagg in Oracle SQL

Use the following query where you will directly get concatenated column values:

SELECT
    "GROUP",
    LISTAGG(VAL_1 || VAL_2 || VAL_3)  
        WITHIN GROUP(ORDER BY VAL_1) AS "TEXT"
FROM DATA
GROUP BY "GROUP";

Note: Do not use oracle reserved keywords as the column names. Here GROUP is the oracle reserved keyword.

Cheers!!

SQL Server Concatenate Multiple Rows By Group Using FOR XML PATH

You need a correlated subquery:

SELECT hs.department AS department,
       STUFF( (SELECT ', ' + top_employee
               FROM findhighest_secondstep hs2
               WHERE hs2.department = hs.department
               FOR XML PATH('')
              ), 1, 2, ''
            ) as top_employees                   
FROM findhighest_secondstep hs
GROUP BY hs.department

Concatenating all rows within a group using dplyr

You were kind of close!

library(tidyr)
library(dplyr)

data <- read_csv('data.csv')
byHand <- group_by(data, hand_id) %>%
    summarise(combo_1 = paste(card_id, collapse = "-"), 
              combo_2 = paste(card_name, collapse = "-"),
              combo_3 = paste(card_class, collapse = "-"))

or using summarise_each:

 byHand <- group_by(data, hand_id) %>%
        summarise_each(funs(paste(., collapse = "-")))

PowerQuery: How can I concatenate grouped values?

If your table is Source, and if NumberColumn has the number type, then this will work:

= Table.Group(Source, {"LetterColumn"}, {{"Column", each Text.Combine(List.Transform(_[NumberColumn], (x) => Number.ToText(x)), ","), type text}})

Table.Group does a group by operation, which creates a table made up of all of the rows with the same value in LetterColumn. _[NumberColumn] gives a list of the values in the NumberColumn column in this new table. The List.Transform part turns the numbers into text values, and Text.Combine joins those numbers together, with a comma separating each value.

If you need the surrounding quotes as well, you can do this:

= Table.Group(Source, {"LetterColumn"}, {{"Column", each """" & Text.Combine(List.Transform(_[NumberColumn], (x) => Number.ToText(x)), ",") & """", type text}})

"""" represents the " character, and & combines two text values.

---

Related Topics