( Select SA.AttributeName + ':' + SAV.AttributeValue + ', <br>'
  FROM Core.ProductAttributeValue PAV
  Join Core.AttributeValue SAV on SAV.AttributeValueId = PAV.AttributeValueId
  Join Core.Attribute SA on SA.AttributeID = SAV.AttributeId
  where PAV.ProductID = P.ProductId and PAV.Version = P.Version
  FOR XML PATH('')) As Attributes,    

--Simple Version
(
  Select T.ColToConcat + ","
  From SubqueryTable T
  Where T.Id = MainQuery.Id
  FOR XML PATH('')
) as Concat

TSQL to Concatenate Subquery Row Values into a Column Value

sql

SQL