Hi,
Say I have some standard SQL that returns:
Smith Parameter1 Value1
Smith Parameter2 Value2
Smith Parameter3 Value3
Jones Parameter1 Value1
Jones Parameter2 Value2
I want the output to display as:
Smith Parameter1 Value1 Parameter2 Value2 Parameter3 Value3
Jones Parameter1 Value1 Parameter2 Value2
I have found that the XMLAGG and XMLELEMENT functions do the trick:
SELECT e.EMPLOYEE, RTRIM(XMLAGG(XMLELEMENT(my_element, p.P_NAME||' '||v.P_VALUE||' ')).extract('//text()'), ' ')
FROM my_employees e, my_parameters p, my_parameter_values v
WHERE v.P_ID = p.P_ID
AND v.EMP_ID = e.EMP_ID
GROUP BY e.EMPLOYEE
The only thing is, the order the SQL originally orders the rows is not reflected when they are merged into a single row, so I may get:
Smith Parameter1 Value1 Parameter3 Value3 Parameter2 Value2
Jones Parameter2 Value2 Parameter1 Value1
It comes back in the correct order if I run the SQL without the XMLAGG/XMLELEMENTS functions, so I guess it's an issue with the way the XMLAGG function parses the data?
Is there anything I can do to resolve this or is there another approach (in 9i) that I can use to achieve the required result?
Many thanks,
Bagpuss
Edited by: Bagpuss on 22-Jul-2010 03:36
Edited by: Bagpuss on 22-Jul-2010 03:39