Thanks for the quick reply, Reeves! This is great!

I have a few other small Biml questions, if you don’t mind:

1.Can you use Biml to create SSRS reports?

2.What version of...

This was an excellent walkthrough. But I have a question: If you have a Biml file with BimlScript in it, is there any way to see the Biml...

Actually, I just realized that doing it for multiple columns is as easy as adding another FOR XML (and including a DISTINCT to prevent dups):

with produce (id,fruit,city)

as (

SELECT 101,'Apple','Cleveland'

UNION ALL...

Much thanks, Adam Haines, for the XMl rollup example.

Any way to get it for multiple columns? As in raw data:

101,'Apple','Cleveland'

101,'Apple','Pittsburgh'

101,'Banana','Pittsburgh'

102,'Grape','Cleveland'

102,'Melon','Cleveland'

103,'Melon','Cleveland'

103,'Melon','Pittsburgh'

results in:

101 'Apple, Banana', 'Cleveland, Pittsburgh'

102 'Grape, Melon', 'Cleveland'

103 'Melon',...

(haven't read through all the posts; sorry if someone already has this)

I've modified so that there is aggregation to an ID:

IF EXISTS (SELECT * FROM sys.objects

WHERE object_id...

What about if you had rows that you wanted to aggregate like this:

ID NAME

101 Apple

101 Banana

102 Orange

102 Melon

102 Grape

And wanted a result set like:

ID NAMES

101 Apple,...