I need some help to transform some XML that is derived from a recordset 
where each record is a Record element. My output XML needs to have one 
Record element for each distinct Id value and within this element I want a 
ColValuesA element that contains a distinct list of all ColA values and a 
ColValuesB element that contains a distinct list of all ColB values.

Recordset used to generate Input XML:

Id    ColA    ColB
1    A1        B1
1    A1        B2
1    A1        B3
1    A2        B1
1    A2        B2
1    A2        B3
2 ...more data...


Input XML:
<Root>
    <Record Id="1">
        <ColA>A1</ColA>
        <ColB>B1</ColB>
    </Record>
 ...and repeated for each record...
</Root>


Output XML required:
<Transformed>
    <Record Id="1">
        <ColValuesA>
            <ColA>A1</ColA>
            <ColA>A2</ColA>
        </ColValuesA>
        <ColValuesB>
            <ColB>B1</ColB>
            <ColB>B2</ColB>
            <ColB>B3</ColB>
        </ColValuesB>
 </Record>
 <Record Id="2">
    ...more...
 </Record>
</Transformed>