查询语句块范例 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