oracle: how to retrieve a pseudo column based on a condition when retrieving data using the SELECT query

The version of the database was never mentioned, but it was tested in Oracle Live Sql 18.4 (the Sql problem is inactive). By using the analytic function listagg, which is available in Oracle 11gr2 and above

                CREATE TABLE product (id char (2), sku char (4)) NOLOGY;

Insert everything
IN VALUES of the product (& # 39; P1 & # 39 ;, & # 39; P1S1 & # 39;)
IN VALUES of the product (& # 39; P1 & # 39 ;, & # 39; P1S2 & # 39;)
IN VALUES of the product (& # 39; P1 & # 39 ;, & # 39; P1S3 & # 39;)
IN VALUES of the product (& # 39; P1 & # 39 ;, & # 39; P1S4 & # 39;)
IN VALUES of the product (& # 39; P2 & # 39 ;, & # 39; P2S1 & # 39;)
IN VALUES of the product (& # 39; P2 & # 39 ;, & # 39; P2S2 & # 39;)
IN VALUES of the product (& # 39; P2 & # 39 ;, & # 39; P2S3 & # 39;)
IN VALUES of the product (& # 39; P2 & # 39 ;, & # 39; P2S4 & # 39;)
SELECT * FROM dual;


WITH result: the analytic function listagg concatenates all sku for the same ID delimited by /
AS (
SELECT ID, LISTAGG (sku, & # 39; / & # 39;)
WITHIN THE GROUP (ORDER BY SKU) AS XYZ
Of product
GROUP BY ID
)

SELECT p.id,
p.sku,
REGEXP_REPLACE (TRIM (BOTH & # 39; / & # 39; FROM (REPLACE (r.xyz, p.sku))), & # 39; // + & # 39 ;, & # 39; / & # 39; ) xyz: the replacement function replaces sku of xyz and regexp_replace eliminates unnecessary & # 39; / & # 39;
FROM product p, result r
WHERE p.id = r.id;

Result of the previous query

ID SKU XYZ
P1 P1S1 P1S2 / P1S3 / P1S4
P1 P1S2 P1S1 / P1S3 / P1S4
P1 P1S3 P1S1 / P1S2 / P1S4
P1 P1S4 P1S1 / P1S2 / P1S3
P2 P2S1 P2S2 / P2S3 / P2S4
P2 P2S2 P2S1 / P2S3 / P2S4
P2 P2S3 P2S1 / P2S2 / P2S4
P2 P2S4 P2S1 / P2S2 / P2S3

Sqlfiddle in 11gR2 SqlFiddle