查询语句块范例 SQL 根据分组列 拼接其他列数据

范例:

create table tb(id int, value varchar(10))

insert into tb values(1,'aa')
insert into tb values(1,'bb')
insert into tb values(2,'aaa')
insert into tb values(2,'bbb')
insert into tb values(2,'ccc')
GO

SELECT id, value = stuff((SELECT ',' + value FROM tb AS t
 WHERE     t .id = tb.id FOR xml path('')), 1, 1, '')
FROM         tb
GROUP BY id
效果如下:


范例也有了,但是我实际项目比上面要复杂些.

select itemCode_C,P_itemCode_C,sys_tbxh_C,sys_tbxhgg_C from ProjectInformation,Project_PMana_New
where ProjectInformation.itemCode_C = Project_PMana_New.P_itemCode_C
group by itemCode_C,P_itemCode_C,sys_tbxh_C,sys_tbxhgg_C
order by itemCode_C
执行下 



要求是 itemCode_C相同的,sys_tbxhgg_C 累加合并.


带入上面的范例

;with cte as 
(
select 
    t1.itemCode_C, t2.P_itemCode_C,  
	sys_tbxh_C,  sys_tbxhgg_C 
from ProjectInformation as t1
inner join Project_PMana_New as t2
  on t1.itemCode_C = t2.P_itemCode_C
group by t1.itemCode_C, t2.P_itemCode_C,
    sys_tbxh_C,sys_tbxhgg_C
)

SELECT 
    itemCode_C, P_itemCode_C,  
	sys_tbxh_C, sys_tbxhgg_C  = stuff((SELECT ',' + sys_tbxhgg_C FROM cte AS t
 WHERE t.itemCode_C = cte.itemCode_C FOR xml path('')), 1, 1, '')
FROM  cte
GROUP BY itemCode_C, P_itemCode_C, sys_tbxh_C
ORDER BY itemCode_C 



效果还是很好的.再稍微修改下,数据整理出来了,需要把拼接好的列,赋值ProjectInformation表的sys_tbxh_C列 就大功告成了..

;with cte as 
(
select 
    t1.itemCode_C, t2.P_itemCode_C,  
	sys_tbxh_C,  sys_tbxhgg_C 
from ProjectInformation as t1
inner join Project_PMana_New as t2
  on t1.itemCode_C = t2.P_itemCode_C
group by t1.itemCode_C, t2.P_itemCode_C,
    sys_tbxh_C,sys_tbxhgg_C
)

update ProjectInformation
set sys_tbxh_C = cxp2.sys_tbxhgg_C
from ProjectInformation,
(SELECT 
    itemCode_C, P_itemCode_C,  
	sys_tbxh_C, sys_tbxhgg_C  = stuff((SELECT ',' + sys_tbxhgg_C FROM cte AS t
 WHERE t.itemCode_C = cte.itemCode_C FOR xml path('')), 1, 1, '')
FROM  cte
GROUP BY itemCode_C, P_itemCode_C, sys_tbxh_C) as cxp2
where ProjectInformation.itemCode_C = cxp2.itemCode_C


评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值