SQL Server - Combine rows into one field for each customer

Question

On SQL Server 2012, when I run:

SELECT Customer, Fruit
FROM Customers_DB

I get the following as outputs:

| Customer      | Fruit     |
| A0001         | Apple     |
| A0001         | Pear      |
| A0002         | Banana    |
| A0003         | Pear      |
| A0004         | Grape     |
| A0004         | Apricot   |

How would I achieve the following outputs, dynamically?

| Customer      | Fruit           |
| A0001         | Apple + Pear    |
| A0002         | Banana          |
| A0003         | Pear            |
| A0004         | Apricot + Grape |

I note that the Fruit are concatenated (maybe Coalesce'd in alphabetical order).

https://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-microsoft-sql-server-2005 — Sean Lange, Oct 19 '17 at 15:22
@SeanLange Then why have you only posted one solitary question and answer? — noobmaster69, Oct 19 '17 at 15:26
Because I didn't feel I needed to do your research. I provided you an excellent example of how to do this. I figured instead of marking this as a duplicate I would point you to a solution. — Sean Lange, Oct 19 '17 at 15:28

Kannan Kandasamy · Answer 1 · 2017-10-19T15:33:30.587

You can use stuff as below:

Select Customer, 
    stuff((select ' + '+Fruit from #customer_db c where c.customer = c1.customer order by Fruit for xml path('')),1,3,'') as Fruit
from #customer_db c1
group by customer

Output as below:

+----------+-----------------+
| Customer |      Fruit      |
+----------+-----------------+
| A0001    | Apple + Pear    |
| A0002    | Banana          |
| A0003    | Pear            |
| A0004    | Apricot + Grape |
+----------+-----------------+

If you are using SQL Server 2017 or SQL Azure then you can use String_agg

SQL Server - Combine rows into one field for each customer

1 Answers1