CODEWITHSUNDEEP
sqlsql-server
noobmaster69
noobmaster69

Reputation: 3115

SQL Server - Combine rows into one field for each customer

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).

Upvotes: 1

Views: 32

Answers (1)

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13969

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

Upvotes: 2

Related Questions