Merge duplicate records into 1 field

Question

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

You've got plenty of good answers below, so nothing to add there, but I'd like to point out that your data model is pretty much the textbook "what not to do" in data modeling 101. The CustomerID and CustomerName look like a pretty clear and unnecessary violation of DB normalization. — Tom H, Feb 09 '09 at 14:39

score 3 · Answer 1 · edited May 23 '17 at 10:32

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.

Thanks for the link about group_concat - gives another keyword to google for (although I think Tomalak's post above probably summarises the options in one place). — Dan, Feb 09 '09 at 10:22
Agree that Tomalak's answer with the link gives you a lot of options to try — Russ Cam, Feb 09 '09 at 10:58

score 1 · Answer 2 · answered Feb 09 '09 at 09:32

1

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.

answered Feb 09 '09 at 09:32

Learning

8,029
3
35
46

score 0 · Answer 3 · answered Feb 09 '09 at 09:51

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.

Merge duplicate records into 1 field

3 Answers3

Linked

Related