Monday, March 18, 2013 9:25 PM
im trying get comma separate list using the following function. it works if i select column but im trying to select two columns and it is not working.
any idea how do i select more than one column?
SELECT T.ID, SUBSTRING(l.LegalEntityNames,1,len(LegalEntityNames)-1) AS 'LegalEntityNames', SUBSTRING(l.PropertyNames,1,len(PropertyNames)-1) AS 'PropertyNames', FROM @Temp T OUTER APPLY( SELECT T1.TMPClientName + ',', T1.TMPPropertyName + ',', FROM @Temp T1 WHERE T1.ID = T.ID FOR XML PATH('')) AS l(LegalEntityNames) GROUP BY l.LegalEntityNames
Monday, March 18, 2013 9:49 PMModeratorYou will need two separate OUTER APPLY and FOR XML PATH('') statements for each of the columns separately.
For every expert, there is an equal and opposite expert. - Becker's Law
- Marked As Answer by lax4u Friday, March 22, 2013 6:56 PM
Monday, March 18, 2013 9:54 PM
This is not possible within a single FOR XML query. You may use a second OUTER APPLY query and ORDER BY TMPClientName ASC, TMPPropertyName ASC on both sub-queries.