Menu
I have the below table:
Part_number seq_nbr Super_narrative
1 1 When replacing
1 2 part with following parts
1 3 note:follwing are now available
1 4 with the organization
I am using the XMLAGG to aggregate the super_narrative in 1 line corresponding to part_number.
SELECT serv_part_nbr, rtrim (xmlagg (xmlelement (e, Super_narrative || ', ')).extract ('//text()'), ',') PHRASE_TEXT
FROM BL_MACS_SUPERSEDENCE_NOTE
GROUP BY part_number
But I want to aggregate the super_narrative according to seq_nbr.
How to do that??
454
The XMLAGG function takes a list of XML elements from one column and returns an aggregated XML document in a single cell.
We can't eliminate duplicates inside XMLAGG. For LISTAGG, we can use the DISTINCT keyword. The return data type of XMLAGG is always XML. So depending on how the result is used, the output of XMLAGG might need to be cast to the data type that the application requires.
The XMLAGG function is used to perform an aggregate of multiple rows. This can used for multiple purpose such as constructing XML value or merging rows string values into a single row value.
The XMLAGG function is used to perform an aggregate of multiple rows. This can used for multiple purpose such as constructing XML value or merging rows string values into a single row value. The XMLAGG function concatenates values of a table column from multiple rows into a single string.
The XMLAGG function concatenates values of a table column from multiple rows into a single string. The ORDER BY clause is applied to the query result set after all aggregate fields are evaluated, ORDER BY cannot directly affect the sequence of values within this string.
The xmlagg creates an XML snippet that is an aggregation of the x XML elements. “.extract (‘//text ()’)” get rids of xml keeping the string alone. Finally we will remove trailing comma in the aggregated text using rtrim.
We can use the XMLAGG function in the oracle database to aggregate the collection of fragments of XML. This function returns the value which will be an aggregated document in XML and if any of the arguments that are passed to this function evaluate to NULL then the argument is skipped or dropped out from the final result.
You don't mention DBMS, but a wild guess would be:
SELECT serv_part_nbr
, rtrim (xmlagg (xmlelement(e, Super_narrative || ', ') order by seq_nbr ).extract ('//text()'), ','
) PHRASE_TEXT
FROM BL_MACS_SUPERSEDENCE_NOTE
GROUP BY part_number
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
