Rows as Columns Pivot Table

Hi,

I want to generate the following xml using the xml export function which will get its data from a db query which uses two tables, machines and images. They are linked via a left join.

indent preformatted text by 4 spaces
<Root>
<Item>
<MakeCol>TestMake</MakeCol>
<ModelCol>TestModel</ModelCol>
<Files>
<ImageCol>image1</ImageCol>
<ImageCol>image2</ImageCol>
<ImageCol>image3</ImageCol>
</Files>
<Item>
</Root>

If I query machines and get a result for a single machine with three images I get the following result as expected;

MakeCol   ModelCol   ImageCol
-------   ---------  --------
TestMake  TestModel  image1
TestMake  TestModel  image2
TestMake  TestModel  image3

I’m not sure how I get the images to list as columns, I have read about pivot tables but I can’t seem to figure out a method to get the below.

MakeCol   ModelCol   ImageCol   ImageCol   ImageCol 
-------   ---------  --------   --------   --------
TestMake  TestModel  image1     image2     image3

The xml I am trying to copy also has the image files wrapped in a tag and again I’m not sure how this is achieved, is it a repeat of some sort?

Any suggestions or ideas would be very much appreciated.

CK