Merge duplicate records into 1 field

问题:

I have a database table that contains a list of contacts, some of those contacts might have multiple records, e.g.

CustomerID, CustomerName, Vehicle
1, Dan, Mazda
1, Dan, Suzuki
2, John, Ford
3, Dasha, Volvo
3, Dasha, Ford

Can I write a select query to return the distinct customerID and CustomerName, and a list of vehicles in 1 record? i.e.

1, Dan, Mazda+Suzuki
2, John, Ford
3, Dasha, Volvo+Ford

Thanks

回答1:

There are some nice answers to this problem on another question.

According to the linked article, this will only work in SQL Server 2005 onwards due to the use of the XML functions. From the article -

SELECT table_name, 
       LEFT(column_names,LEN(column_names) - 1)   AS column_names 
FROM   (SELECT table_name, 
               (SELECT column_name + ',' AS [text()] 
                FROM   information_schema.columns AS internal 
                WHERE  internal.table_name = table_names.table_name 
                FOR xml PATH ('') 
               ) AS column_names 
        FROM   (SELECT   table_name 
                FROM     information_schema.columns 
                GROUP BY table_name) AS table_names) AS pre_trimmed;

Second version (admittedly inspired by this post, which I stumbled on after writing the first version):

SELECT table_name, 
       LEFT(column_names,LEN(column_names) - 1)   AS column_names 
FROM   information_schema.columns AS extern 
       CROSS APPLY (SELECT column_name + ',' 
                    FROM   information_schema.columns AS intern 
                    WHERE  extern.table_name = intern.table_name 
                    FOR XML PATH('') 
                   ) pre_trimmed (column_names) 
GROUP BY table_name,column_names;

The second CROSS APPLY version is supposedly slower according to the execution plan, but I didn’t conduct any benchmarks at all.

回答2:

I'm not sure if it can be done via a single sql. However , last time when I attempted this in sybase's tsql , I had used temp tables and cursors. So it can be done atleast using that route.

回答3:

I'm pretty sure you can't do it via a single SQL query.

If it's a regular requirement, a Function should be made which is called for each customer ID.

SELECT   abc.CustomerID,
         dbo.udf_getCSVCustomerVehicles(abc.customerID)
FROM     (SELECT DISTINCT CustomerID
          FROM   TABLE) abc
ORDER BY abc.CustomerID

Then just create a scalar function which coalesces the values together into a variable and returns the result.