存储过程计数中包含id号的行数

你可以试试这个

    SELECT   
        InsuranceId
        , count(claimid) as TotalPendingClaims
        , claimids = STUFF((SELECT ',' + cast(claimid as varchar(255)) FROM Claim B WHERE B.InsuranceId = A.InsuranceId FOR XML PATH('')) , 1 , 1 , '' )
            From Claim A
            Group by InsuranceId

尝试此查询

SELECT InsuranceId, name, COUNT(*) TotalPendingClaims, 
ClaimIds = STUFF((SELECT ', ' + CONVERT(varchar, claimid)
           FROM  dbo.Claim c2
           WHERE c1.InsuranceId = c2.InsuranceId AND c1.name = c2.name
           FOR XML PATH('')), 1, 2, '')
FROM claim c1
GROUP BY InsuranceId, name
ORDER BY InsuranceId, name

您可以使用下面的查询,在该查询中,stuff连接声明ID,并且可以单独计算计数

SELECT DISTINCT t2.InsuranceId ,
    STUFF((Select ','+ CONVERT(VARCHAR(5), claimid) FROM #TEMP_INSURANCE T1 
WHERE T1.InsuranceId=T2.InsuranceId FOR XML PATH('')),1,1,'')  AS ClaimID,
    Cnt
FROM #TEMP_INSURANCE t2 INNER JOIN
    (SELECT InsuranceId,COUNT(claimid) Cnt FROM #TEMP_INSURANCE GROUP BY InsuranceId)T ON t.InsuranceId = t2.InsuranceId

我的问题的答案是:

--changes addded by Ali Imran 12/13/2017 get also the claim number

claimid=stuff
(
 ( select distinct','+cast(c1.claimid as varchar(max))
  from #Claim c1 where
  c1.insuranceid=ins.insuranceid

  for xml path('')
  ),1,1,''
),

--Changes ended by Ali Imran