把多行的两列数据分别合并

[chrissky2006]

把jfmc、khmc相同的多行数据进行汇总，cfhjj和fzbyyl列分别进行合并，其他列是求和。
1、我使用如下语句先处理cfhjj列

--将cfhjj列合并

SELECT a.khmc,a.jfmc,SUM(bysjs)as bysjs,SUM(byjds) as byjds,cfhjj=( SELECT cfhjj +','
FROM #TEMP AS b
WHERE b.KHMC = a.KHMC AND b.jfmc=a.jfmc
FOR XML PATH('') ), fzbyyl,SUM(zsjs) as zsjs,SUM(zjds) as zjds,SUM(zdk) as zdk,SUM(zfh) as zfh

into #temp2
FROM #TEMP AS a
GROUP BY jfmc,khmc ,fzbyyl
2、得到了如下结果，cfhjj列合并了两次
DROP TABLE #TEMP
3、合并fzbyyl列
--将fzbyyl列合并
SELECT a.khmc,a.jfmc,SUM(bysjs)as bysjs,SUM(byjds) as byjds,cfhjj,fzbyyl=( SELECT fzbyyl +','
FROM #temp2 AS b
WHERE b.KHMC = a.KHMC AND b.jfmc=a.jfmc
FOR XML PATH('') ),SUM(zsjs) as zsjs,SUM(zjds) as zjds,SUM(zdk) as zdk,SUM(zfh) as zfh
from #temp2 as a
group by jfmc,khmc,cfhjj
drop table #temp2

4、请各位大侠帮帮忙啊！

[道玄希言]

;with tb01(jfmc, khmc, bysjs, byjds, bydk, cfhjj, fzbyyl, zsjs, zjds, zdk, zfh) as
(
    select 'DC1M1219', 'CCB', 0, 0, 0, '', '', 2, 2, 4, 0 union all
    select 'DC1M1219', 'CCB', 0, 0, 0, 'H15', 'H', 1, 1, 2, 500 union all
    select 'DC1M1219', 'CCB', 0, 0, 0, 'H16', '', 1, 1, 2, 1000 union all
    select 'DC1M2221', 'WJB', 1, 1, 4, '', '', 1, 1, 4, 500 union all
    select 'DC1M2221', 'WJB', 1, 1, 4, 'A01', '', 1, 1, 4, 500 union all
    select 'DC1M2221', 'WJB', 1, 1, 2, 'H01', 'H', 1, 1, 2, 500
)

--select * from tb01

select jfmc, khmc, 
  SUM(bysjs)as bysjs, SUM(byjds) as byjds, 
  SUM(zsjs)as zsjs, SUM(zjds)as zjds, 
  SUM(zdk)as zdk, SUM(zfh)as zfh,
  (
    stuff((select N','+ cfhjj   
      from tb01   
      where jfmc=t.jfmc and khmc=t.khmc and cfhjj <> ''    
        order by cfhjj desc   
    FOR XML PATH('')), 1, 1, N'')  
  ) as cfhjj, 
  (
    stuff((select N','+ fzbyyl   
      from tb01   
      where jfmc=t.jfmc and khmc=t.khmc and fzbyyl <> ''     
        order by fzbyyl desc   
    FOR XML PATH('')), 1, 1, N'')  
  ) as fzbyyl 
from tb01 as t
group by jfmc, khmc

结果：
jfmc	khmc	bysjs	byjds	zsjs	zjds	zdk	zfh	cfhjj	fzbyyl
DC1M1219	CCB	0	0	4	4	8	1500	H16,H15	H
DC1M2221	WJB	3	3	3	3	10	1500	H01,A01	H

[Tiger_Zhao]

不要混在一起。
把分组合计做成一个子查询，把cfhjj、fzbyyl的列合并各自做一个子查询，最后再把3个子查询并起来。

[LongRui888]

你把建表语句，和insert语句都贴出来，然后给出合并后，你想要的结果。