sqlserver汇总后多行转一行（Oracle的WM_CONCAT()的sqlserver方法）

create table testsqdquery(
sqdid varchar(36),
cxdbh varchar(20)
)

insert into testsqdquery(sqdid, cxdbh) values('id001','001');
insert into testsqdquery(sqdid, cxdbh) values('id001','0001');
insert into testsqdquery(sqdid, cxdbh) values('id002','002');
insert into testsqdquery(sqdid, cxdbh) values('id002','0002');
insert into testsqdquery(sqdid, cxdbh) values('id002','00002');
insert into testsqdquery(sqdid, cxdbh) values('id003','003');
insert into testsqdquery(sqdid, cxdbh) values('id004','004');

得到结果如下

想得到的效果如下：

那么，SQL的写法如下：

SELECT 
B.sqdid,
LEFT(cxdbh,LEN(cxdbh)-1) as cxdbh 
FROM (
SELECT sqdid,
(SELECT cxdbh+',' FROM testsqdquery  WHERE sqdid=A.sqdid  FOR XML PATH('')) AS cxdbh
FROM testsqdquery A 
GROUP BY sqdid
) B;

如果只执行里面的SQL：

SELECT sqdid,
(SELECT cxdbh+',' FROM testsqdquery WHERE sqdid=A.sqdid FOR XML PATH('')) AS cxdbh
FROM testsqdquery A 
GROUP BY sqdid

得到如下结果：