question

I can think of 3 ways around this. i) Only select one column from the subquery ii) If you do need them both, then repeat the subquery, returning one column each time SELECT DISTINCT Role ,AppID ,AppName ,Name ,Type ,CASE WHEN Value '' THEN Value END AS Value ,CASE WHEN MapType = 'Mapping' THEN SourceName ELSE CalcOption END AS SourceAttribute ,(SELECT SourceValue FROM #m attr1 WHERE attr1.RoleID = r.RoleID AND attr1.AppIDID = r.AppIDID AND attr1.AttributeID = r.AttributeID AND attr1.AttributeValue = r.AttributeValue AND Type = 'Mapping') AS "Map/Input SourceValue" ,(SELECT AttributeValue FROM #m attr1 WHERE attr1.RoleID = r.RoleID AND attr1.AppIDID = r.AppIDID AND attr1.AttributeID = r.AttributeID AND attr1.AttributeValue = r.AttributeValue AND Type = 'Mapping') AS "Map/Input AttributeValue" FROM #m r ORDER BY RoleName FOR XML PATH ('RoleAttributes'), ROOT('Role') iii) Use an CROSS APPLY or OUTER APPLY to get the values SELECT DISTINCT Role ,AppID ,AppName ,Name ,Type ,CASE WHEN Value '' THEN Value END AS Value ,CASE WHEN MapType = 'Mapping' THEN SourceName ELSE CalcOption END AS SourceAttribute ,[MapInput].SourceValue ,[MapInput].AttributeValue FROM #m r outer apply (SELECT SourceValue ,AttributeValue FROM #m attr1 WHERE attr1.RoleID = r.RoleID AND attr1.AppIDID = r.AppIDID AND attr1.AttributeID = r.AttributeID AND attr1.AttributeValue = r.AttributeValue AND Type = 'Mapping') [MapInput] ORDER BY RoleName FOR XML PATH ('RoleAttributes'), ROOT('Role') You may also need to limit those subqueries with a TOP 1 ? Or is that the problem - you WANT multiple values?