将重复记录合并到 1 个字段中 [英] Merge duplicate records into 1 field

问题描述

我有一个包含联系人列表的数据库表，其中一些联系人可能有多个记录，例如

CustomerID, CustomerName, Vehicle1、丹、马自达1、丹、铃木2、约翰福特3、大沙、沃尔沃3、大夏、福特

我可以编写一个选择查询来返回不同的 customerID 和 CustomerName，以及 1 条记录中的车辆列表吗?即

1，丹，马自达+铃木2、约翰福特3、大夏、沃尔沃+福特

谢谢

解决方案

有一些在另一个问题上很好地回答了这个问题.

根据链接文章，这由于使用了 XML 函数，因此只能在 SQL Server 2005 中运行.来自文章-

SELECT table_name,左(column_names,LEN(column_names) - 1) AS column_namesFROM (SELECT table_name,(SELECT column_name + ',' AS [text()]FROM information_schema.columns AS 内部WHERE internal.table_name = table_names.table_nameFOR xml 路径 ('')) AS column_namesFROM (SELECT table_nameFROM information_schema.columnsGROUP BY table_name) AS table_names) A​​S pre_trimmed;

<块引用>

第二个版本(诚然灵感来自这篇文章，这是我后来偶然发现的写第一个版本):

SELECT table_name,左(column_names,LEN(column_names) - 1) AS column_namesFROM information_schema.columns AS externCROSS APPLY (SELECT column_name + ','FROM information_schema.columns 作为实习生WHERE extern.table_name = intern.table_nameFOR XML 路径('')) pre_trimmed (column_names)GROUP BY table_name,column_names;

<块引用>

第二个 CROSS APPLY 版本是据推测，速度较慢执行计划，但我没有执行任何基准.

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

解决方案

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.

这篇关于将重复记录合并到 1 个字段中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！