Combining rows and in SQL

Question

My data is as follows:

**Name** **DOB**   **B1**  **C1**
cust A   01/06/99    ba1     ca1
cust A   01/06/99    ba2     ca2

I need to combine the rows for columns b1 and c1 within one row with the result looking like:

**Name** **DOB**   **B1**  **C1**
cust A   01/06/99  ba1, ba2  ca1, ca2

I'm tried different ways of concatenating the rows together, but it hasn't seemed to work. There can also be more than 2 rows for a single customer or only 1. Any help is appreciated.

perhaps [this answer](https://stackoverflow.com/questions/273238/how-to-use-group-by-to-concatenate-strings-in-sql-server) can help you with this: — Pedro Manuel Rossi Mercado, Feb 09 '23 at 01:29

score 2 · Answer 1 · answered Feb 09 '23 at 01:29

2

If 2017+ you can use string_agg() in a simple aggregation query.

Select Name 
      ,DOB
      ,B1 = string_agg(B1,', ')
      ,c1 = string_agg(c1,', ')
 From  YourTable
 Group By Name,DOB

answered Feb 09 '23 at 01:29

John Cappelletti

79,615
7
44
66

score 0 · Answer 2 · answered Feb 09 '23 at 02:24

If you do not have SQL Server 2017, you can use stuff for XML to get a comma separated value of your table.

Like this :

; with mytable  as (
select 'Cust A' as cust,   '01/06/99' as dob,    'ba1' as b1,     'ca1' as c1
union all select
'Cust A' as cust,   '01/06/99' as dob,    'ba2' as b1,     'ca2' as c1
)

select distinct
cust
,dob
,

 STUFF(
(
    SELECT ',' + b1
    FROM mytable A1
    WHERE A1.cust = A2.cust FOR XML PATH('')
), 1, 1, '') AS b1


,

 STUFF(
(
    SELECT ',' + c1
    FROM mytable A1
    WHERE A1.cust = A2.cust FOR XML PATH('')
), 1, 1, '') AS c1

FROM mytable A2;

Results :

Combining rows and in SQL

2 Answers2