oracle 相同id合并,SQLServer 合并ID相同行，同ID的列值累加。

;with cte1 as (

select 18 as UserID, 6 as StoreID, N'lema' as StoreName, N'Jayson.Sauve' as AllName,0 as IsManager,0 as IsTeminated,7 as JobID, N'3157067182' as MobilePhone union all

select 19,6,N'lema',N'Becky.Sebert',0,0,9,'704-293-1587' union all

select 32,6,N'lema',N'Kelli.Putnam',0,0,2,'315-750-8786' union all

select 32,6,N'lema',N'Kelli.Putnam',0,0,9,'315-750-8786' union all

select 32,6,N'lema',N'Kelli.Putnam',0,0,20,'315-750-8786' union all

select 33,6,N'lema',N'Kathy.Kruse',0,0,2,'704-455-6123'

)

select

Main.UserID,

Main.StoreID,

Main.StoreName,

Main.AllName,

Main.IsManager,

Main.IsTeminated,

left(Main.JobIDs, len(Main.JobIDs) - 1) as [JobIDs],

Main.MobilePhone

from (

select

UserID, StoreID, StoreName,

AllName, IsManager, IsTeminated,

(

select cast(tmp1.JobID as varchar(10)) + ',' AS [text()]

from cte1 as tmp1

where

tmp1.StoreID = tmp2.StoreID

and tmp1.UserID = tmp2.UserID

order by tmp1.UserID

For XML PATH ('')

) JobIDs,

MobilePhone

from cte1 as tmp2

group by

UserID, StoreID, StoreName,

AllName, IsManager, IsTeminated, MobilePhone

) [Main]

根据UserID 合并

合并前：

合并后：