如何使用 SQL Server 2014 的 UPDATE 合併和刪除 SQL 中的重複行？

例如，我有一個表：

id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web
2  | 23   | xyz  | 0    | mobile
3  | 24   | xyzc | 0    | web
4  | 25   | xyzc | 0    | web

我希望結果是：

id | code | name | type | deviceType
---+------+------+------+-----------
1  | 23   | xyz  | 0    | web&mobile
2  | 24   | xyzc | 0    | web
3  | 25   | xyzc | 0    | web

如何在 SQL Server 中使用UPDATE和DELETE語句執行此操作？

注意：不幸的是，我無法使用給出的解決方案，因為 SQL Server 2014 沒有“STRING_AGG”功能。

任何幫助是極大的讚賞！

請試試這個：

with stable as (
SELECT
   ROW_NUMBER() over(order by [code]) as [ID]
   , [code]
   , [name]
   , [type]
   , CASE
       WHEN (SUM([web]) = 1 AND SUM([mobile]) = 1) THEN 'web&mobile'
       WHEN (SUM([web]) = 1 ) THEN 'web'
       WHEN (SUM([mobile]) = 1) THEN 'mobile'
       ELSE null END AS deviceType
FROM (
   select [id], [code], [name], [type], [web], [mobile]
   from (
       SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
   ) as source
   pivot 
   (
       COUNT(deviceType) FOR deviceType IN ([web],[mobile])
   ) as tb
) source
group by [code], [name], [type])
,ttable as (
SELECT [id], [code], [name], [type], [deviceType] FROM [dbo].[table]
)
MERGE
   INTO ttable as T
   USING stable as S
   ON t.id = S.id
   WHEN MATCHED THEN
       UPDATE SET
           T.[code] = S.[code], T.[name] = S.[name], T.[type] = S.[type], T.[deviceType] = S.[deviceType]
   WHEN NOT MATCHED BY SOURCE THEN
       DELETE 
;

假設您要按 、 和 欄位分組code並name獲取type’ deviceTypes 的聚合，在 SQL Server 2014 中，您可以使用stuffand的這種解決方法FOR XML。這是通用的，適用於該deviceType欄位中的任何值。

與您的範例輸出相比，此解決方案的一個區別是表中的原始行/id 得到更新並刪除了額外的行。在您的範例中，id 1 和 2 組合在一起，因此最終結果保留了第 1 行，即現在的第 1 行和第 2 行，並將 id 2 作為單獨的行刪除。

這是在 SQL Server 2014 中驗證的完全可重現的範例：

create table #tmp (
   id int not null,
   code int not null,
   name nvarchar(255) not null,
   type int not null,
   deviceType nvarchar(255) not null)

insert into #tmp values (1, 23, 'xyz', 0, 'web')
insert into #tmp values (2, 23, 'xyz', 0, 'mobile')
insert into #tmp values (3, 24, 'xyzc', 0, 'web')
insert into #tmp values (4, 25, 'xyzc', 0, 'web')

-- Original table for comparison
select * from #tmp

-- Output grouped by code, name, type, and aggregate deviceType
;with results as (
   select
   min(id) as id,
   code, 
   name, 
   type,
   ( 
       stuff((SELECT '&' + Cast(dt.deviceType AS VARCHAR (300)) [text()]
           FROM   #tmp dt
           WHERE  tmp.code = dt.code
               and tmp.name = dt.name
               and tmp.type = dt.type
           FOR xml path(''),
           type).value('.', 'nvarchar(MAX)'), 1, 1, '')
   ) as [deviceType]
   from #tmp tmp
   group by code, name, type
)
merge into #tmp
using results on #tmp.id = results.id
when matched then
   update set #tmp.deviceType = results.deviceType
when not matched by source then
   delete;

select * from #tmp

drop table #tmp

引用自：https://dba.stackexchange.com/questions/282393