查询语句块范例 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')GOSELECT id, value = stuff((SELECT ',' + value FROM tb AS t WHERE     t .id = tb.id FOR xml path('')), 1, 1, '')FROM         tbGROUP BY id

效果如下:

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

select itemCode_C,P_itemCode_C,sys_tbxh_C,sys_tbxhgg_C from ProjectInformation,Project_PMana_Newwhere ProjectInformation.itemCode_C = Project_PMana_New.P_itemCode_Cgroup by itemCode_C,P_itemCode_C,sys_tbxh_C,sys_tbxhgg_Corder 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 t1inner join Project_PMana_New as t2  on t1.itemCode_C = t2.P_itemCode_Cgroup 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  cteGROUP BY itemCode_C, P_itemCode_C, sys_tbxh_CORDER 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 t1inner join Project_PMana_New as t2  on t1.itemCode_C = t2.P_itemCode_Cgroup by t1.itemCode_C, t2.P_itemCode_C,    sys_tbxh_C,sys_tbxhgg_C)update ProjectInformationset sys_tbxh_C = cxp2.sys_tbxhgg_Cfrom 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  cteGROUP BY itemCode_C, P_itemCode_C, sys_tbxh_C) as cxp2where ProjectInformation.itemCode_C = cxp2.itemCode_C