-1

It is Oracle DB. I have a table as mentioned below

PRODUCT  SKU
P1      P1S1
P1      P1S2
P1      P1S3
P1      P1S4
P2      P2S1
P2      P2S2
P2      P2S3
P2      P2S4

I need an output from the above table using SELECT query as mentioned below

PRODUCT  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

Please assist me on this. Thanks!!

  • You may want to try and explain in human language the logic behind the expected output. That'll get you half way towards writing it as a SQL query. – mustaccio Dec 7 '18 at 19:23
  • Are you looking for a / delimited list of the other SKU's for that same product? – Jonathan Fite Dec 7 '18 at 20:38
  • Yes @ Jonathan Fite – muni7789 Dec 11 '18 at 18:55
3

Never mentioned about database version but tested on Oracle Live Sql 18.4(Sql fiddle is down).By using listagg analytic function which is available in Oracle 11gr2 and above

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

    INSERT ALL 
    INTO product VALUES('P1','P1S1') 
    INTO product VALUES('P1','P1S2') 
    INTO product VALUES('P1','P1S3') 
    INTO product VALUES('P1','P1S4') 
    INTO product VALUES('P2','P2S1') 
    INTO product VALUES('P2','P2S2') 
    INTO product VALUES('P2','P2S3') 
    INTO product VALUES('P2','P2S4') 
    SELECT * FROM dual;


     WITH result  -- listagg analytic function concatenates all sku for same id delimited by /
        AS ( 
            SELECT id,LISTAGG(sku,'/') 
              WITHIN GROUP (ORDER BY sku) AS xyz 
            FROM product 
            GROUP BY id 
            ) 

            SELECT p.id, 
                   p.sku, 
                   REGEXP_REPLACE(TRIM(BOTH '/' FROM (REPLACE(r.xyz,p.sku))),'//+','/') xyz  -- replace function replaces matching sku from xyz and regexp_replace removes unnecessary '/'
            FROM product p,result r 
            WHERE p.id=r.id;

Result of above 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 on 11gR2 SqlFiddle

  • Replace function produces side effect with unnecessary '/' used trim function to remove leading and trailing and regexp_replace to remove extra '/'between strings – Sam Dec 8 '18 at 0:28
  • Thanks Sam!!. The code worked. By the way we are using Oracle 12c – muni7789 Dec 11 '18 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.