sqlserver 中多行数据如何合并成一行?

--病人多诊断合并,切开
select distinct cmzh ,
STUFF((select ',' + [CCFZD] from yxhis2018..TBMZCFHZ201807 where cmzh = a.cmzh for xml path(''))  ,1,1,'') as czd from  yxhis2018..TBMZCFHZ201807 as a

STUFF(abcde,2,3,ijklmn)  a字段从2位开始删除3个插入b  → aijklmne


SELECT DISTINCT  --a.cmzh,
SUBSTRING(a.CCFZD, b.number, CHARINDEX(',', a.CCFZD+',', b.number)-b.number) AS CCFZD
FROM    yxhis2019..TBMZCFHZ201906 a
        JOIN MASTER.dbo.spt_values b
            ON CHARINDEX(',', ','+a.CCFZD, b.number) = b.number
WHERE  b.[type]= 'P' and a.ccfzd <> ''

a列重复 b列不重复 b列任取一行

--病人多个id 任取一个id

select cicid, cxm from (
select cicid,cxm,row_number() over(partition by cxm order by cicid desc) id
from dbo.tbicxx where cxm in('test1','test2')
) a where id =1