首页 新闻 会员 周边 捐助

SQLServer 多行重复数据合并一行

0
悬赏园豆：10 [已解决问题] 解决于 2012-12-16 01:20

如图：

一个人员对应多的jobID时候，把该人员数据合并到一行，jodID以逗号相连。

跪求！！！！

其小本的主页 其小本 | 初学一级 | 园豆：37
提问于：2012-12-14 14:31
< >
分享
最佳答案
1

这样就行了吧

 

SELECT UserId, StoreID, StoreName, IsManager, IsTerminated, MobilePhone
       ( SELECT JobID + ','
          FROM UserTable t2
          WHERE t2.UserID = t1.UserID
          ORDER BY JobID
          FOR XML PATH('') ) AS JobIDs
      FROM UserTable t1
      GROUP BY UserId, StoreID, StoreName, IsManager, IsTerminated, MobilePhone
收获园豆：10
gunsmoke | 老鸟四级 |园豆：3592 | 2012-12-14 16:37
其他回答(3)
0

这个表只有2个字段吗？ userID,jobID？

xu_happy_you | 园豆：222 (菜鸟二级) | 2012-12-14 15:04

图上的列都是这个表的字段呀

支持(0) 反对(0) 其小本 | 园豆：37 (初学一级) | 2012-12-14 15:27
0 
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'

;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 distinct 
    UserID, StoreID, StoreName, 
    AllName, IsManager, IsTeminated, MobilePhone, 
    (
      select cast(tmp1.JobID as varchar(10)) + ',' AS [text()]
      from cte1 as tmp1
      where
        tmp1.UserID = tmp2.UserID
        and tmp1.StoreID = tmp2.StoreID
        and tmp1.StoreName = tmp2.StoreName
        and tmp1.AllName = tmp2.AllName
        and tmp1.IsManager = tmp2.IsManager
        and tmp1.IsTeminated = tmp2.IsTeminated
        and tmp1.MobilePhone = tmp2.MobilePhone
      order by tmp1.UserID
      For XML PATH ('')
    ) JobIDs
  from cte1 as tmp2
) [Main]

不要问我原理。。。我抄老外的改的~我也不懂

dotNetDR_ | 园豆：2078 (老鸟四级) | 2012-12-14 15:26 
with cte as (
  select 1 as JobID union 
  select 2 union
  select 3 union
  select 4 
)
select 
    cast(cte.JobID as varchar(8000)) + ',' as [text()] 
from cte for xml path ('')

你可以单独运行这个脚本~~~这个是 sql server中的 For XML 子句 其他的我就没空帮到你了！希望你能满意！

支持(0) 反对(0) dotNetDR_ | 园豆：2078 (老鸟四级) | 2012-12-14 15:40 
;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]

最后这个版本出来了~~呼呼

支持(0) 反对(0) dotNetDR_ | 园豆：2078 (老鸟四级) | 2012-12-14 15:57
0

你用left join（左链接）去实现，对于UserId列避免重复可以distinact。

select  distinct t1.userID,t1.列名,t2.列名 from 表1 t1 left join 表2 t2 on t1.id=t2.id  left join  ？ on 条件。

根据你自己需求，修改一下就可以了。

伏草惟存 | 园豆：1420 (小虾三级) | 2012-12-14 17:34
清除回答草稿
   您需要登录以后才能回答，未注册用户请先注册。