Stuff () function +for XML PATH () function in SQL multi-line and one-line statistical example

SELECT *  from Tbiz_projectrelation

Target statistics each project has several application records

Step1

SELECTProjectID, Relationids= STUFF((SELECT ',' + CONVERT(VARCHAR,[Relationid]) fromTbiz_projectrelation TWHEREProjectID=T1. ProjectID forXML PATH ("')),1,1,"') fromTbiz_projectrelation T1

Step2

SELECTProjectID, Relationids= STUFF((SELECT ',' + CONVERT(VARCHAR,[Relationid]) fromTbiz_projectrelation TWHEREProjectID=T1. ProjectID forXML PATH ("')),1,1,"') fromTbiz_projectrelation T1GROUP  byProjectID

Step3

SELECTProjectID, Relationids= STUFF((SELECT ',' + CONVERT(VARCHAR,[Relationid]) fromTbiz_projectrelation TWHEREProjectID=T1. ProjectID forXML PATH ("')),1,1,"'),        COUNT([Relationid]) as [COUNT] fromTbiz_projectrelation T1GROUP  byProjectID

Stuff () function +for XML PATH () function in SQL multi-line and one-line statistical example