String concatenation after DISTINCT result selected

Question

I have below query:

SELECT xmlagg(xmlparse(content RESULTS || '|' wellformed) ORDER BY RESULTS).getclobval() AS RESULTS 
FROM
(
  SELECT distinct ' '   || result|| ' - '   || result_final || ' xxx' as RESULTS from myTable where ID = '123456'
);

Currently the " xxx" will append at the end of each result_final, how can achieve by concatenating it to the very beginning of the final string of the query?

' xxx'|| RESULTS 

With condition, above concatenation should only take place when result=x, else only RESULTS should be printed.

Answer 1

Move that string in front of the "result" not behind it.

Though, as additional condition should be met, use your current query (without 'xxx') as a subquery and apply condition via CASE.

With dummy sample data:

SQL> DESC mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 RESULT                                             CHAR(1)
 RESULT_FINAL                                       CHAR(1)
 ID                                                 CHAR(6)

SQL> SELECT * FROM mytable;

R R ID
- - ------
x y 123456

SQL> SELECT CASE
  2            WHEN TO_CHAR (results) = 'x' THEN ' xxx' || results
  3            ELSE results
  4         END AS results
  5    FROM (SELECT XMLAGG (XMLPARSE (CONTENT RESULTS || '|' WELLFORMED)
  6                         ORDER BY RESULTS).getclobval () AS RESULTS
  7            FROM (SELECT DISTINCT
  8                         ' ' || result || ' - ' || result_final AS RESULTS
  9                    FROM myTable
 10                   WHERE ID = '123456'));

RESULTS
--------------------------------------------------------------------------------
 x - y|

SQL>