How to make a Custom variable combining multiple row output into single row and column for output into email?

dkeyser

I think I've seen the answer to this in a tip or another question somewhere but I can't seem to find it. I have a single column but multiple rows of data I would like to turn into just a single column and single row of output so I can use this in an alert email. I seem to remember it might have something to do with a "for xml" tag or something that made it super simple and easy but of course I can't remember it now. Does anyone know how to do this?

    SELECT 
    CMS.FullName
    FROM Orion.ContainerMemberSnapshots CMS
    WHERE
    CMS.Status = 2 AND CMS.Container.DisplayName = 'ContainerName'AND CMS.Name LIKE '%special%'

Query output format

Output1

Output2

Output3

Output4

Output5

Output6

Output7

Format I'm looking for (Would like an output with new lines in it but if it comes out all on one line then that'd be fine as well)

Output1 Output2 Output3 Output4 Output5 Output6 Output7

OR

Output1 Output2 Output3 Output4 Output5 Output6 Output7