How can I trasform foreign key values in one column?

Question

I'm using SQL Server 2008 R2 (SP2) database. I have two tables like this for customer data and his interests:

Table name: Customers

+----+----------+---------+
| Id | Name     | Surname |
+----+----------+---------+
| 1  | Mario    | Rossi   |
+----+----------+---------+
| 2  | Giuseppe | Verdi   |
+----+----------+---------+

Table name: Customer interests

+------------+----------+
| CustomerId | Interest |
+------------+----------+
| 1          | IT       |
+------------+----------+
| 1          | Football |
+------------+----------+
| 1          | Basket   |
+------------+----------+
| 2          | Basket   |
+------------+----------+
| 2          | Bonsai   |
+------------+----------+

I need to create one SQL statement for data extraction. The output result must be like follow:

+----+----------+---------+-----------------------+
| Id | Name     | Surname | Interests             |
+----+----------+---------+-----------------------+
| 1  | Mario    | Rossi   | IT; Football; Basket; |
+----+----------+---------+-----------------------+
| 2  | Giuseppe | Verdi   | Basket; Bonsai;       |
+----+----------+---------+-----------------------+

In essence I would one column with all customer's interests separated by semicolon.

How can I obtain this?

Thanks, Marco

You should be able to use this: http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — mortb, Jul 14 '16 at 10:46

score 0 · Accepted Answer · answered Jul 14 '16 at 10:52

Try following query

SELECT
    Id,
    Name,
    Surname,
    (
        SELECT 
            CI.Interest + '; '
        FROM                
            CustomerInterests CI
        WHERE 
            CI.CustomerId = C.ID
        FOR XML PATH ('')
    ) AS Interest 
FROM
    Customer C

How can I trasform foreign key values in one column?

1 Answers1