Getting Select Result in Multiple Columns and a Single Row

Question

Below I tried to get my Product's sub parts in 3 columns and a single row. I am using 4 tables. STOCK40T is my main table which I get Orders and other informations. I am getting Customer name from table CUSTOM00. Stock name and products' details from table STOCK00. Finally, I am using table BOMU01T to get product tree of my product. 'BOMREC_SOURCECODE' is the column where sub parts' stored in BOMU01T.

SELECT * FROM (
SELECT  
      SL.DOCNO AS DOCUMENT_NO,
      SL.CUSTOMERCODE AS CUSTOMER_CODE,
      C0.NAME AS CUSTOMER_NAME,
      SL.CODE AS PRODUCT_CODE,
      ST.NAME AS PRODUCT_NAME,
      SL.QUANTITY AS ORDER_QUANTITY,
      SL.QT_SHIPPED AS SHIPPED_QUANTITY,
      (SL.QUANTITY-SL.QT_SHIPPED) AS OPEN_ORDER_QUANTITY,
      NVL(TO_CHAR(TO_DATE(SL.DELVRDATE,'YYYY/MM/DD'),'DD/MM/YYYY'),0) AS DELIVERY_DATE,
      (SELECT CODE FROM STOCK00 S0 WHERE S0.CODE=BOM.BOMREC_SOURCECODE AND TRIM(S0.GK_7)='PAINTED') AS PAINTED,
      (SELECT CODE FROM STOCK00 S0 WHERE S0.CODE=BOM.BOMREC_SOURCECODE AND TRIM(S0.GK_13)='RAWM-1') AS RAWM-01,
      (SELECT CODE FROM STOCK00 S0 WHERE S0.CODE=BOM.BOMREC_SOURCECODE AND TRIM(S0.GK_16)='RAWM-02') AS RAWM-02
      
FROM STOCK40T SL

LEFT JOIN STOCK00 ST ON TRIM(ST.CODE)=TRIM(SL.CODE)
LEFT JOIN CUSTOM00 C0 ON TRIM(C0.CODE)=TRIM(SL.CUSTOMERCODE)
LEFT JOIN BOMU01T BOM ON BOM.BOMREC_CODE=SL.CODE
LEFT JOIN STOCK00 S0 ON S0.CODE=BOM.BOMREC_SOURCECODE
              )
ORDER BY 4, 9 ASC

I am getting this result at the moment.

What I want should look like this.

Answer 1

Since we don't have the sample data with us, I still think you can try the aggregation to achieve your desired result -

SELECT DOCUMENT_NO,
       CUSTOMER_CODE,
       CUSTOMER_NAME,
       PRODUCT_CODE,
       PRODUCT_NAME,
       ORDER_QUANTITY,
       SHIPPED_QUANTITY,
       OPEN_ORDER_QUANTITY,
       DELIVERY_DATE,
       MAX(PAINTED),
       MAX(RAWM-01),
       MAX(RAWM-02)
  FROM (SELECT SL.DOCNO AS DOCUMENT_NO,
               SL.CUSTOMERCODE AS CUSTOMER_CODE,
               C0.NAME AS CUSTOMER_NAME,
               SL.CODE AS PRODUCT_CODE,
               ST.NAME AS PRODUCT_NAME,
               SL.QUANTITY AS ORDER_QUANTITY,
               SL.QT_SHIPPED AS SHIPPED_QUANTITY,
               (SL.QUANTITY-SL.QT_SHIPPED) AS OPEN_ORDER_QUANTITY,
               NVL(TO_CHAR(TO_DATE(SL.DELVRDATE,'YYYY/MM/DD'),'DD/MM/YYYY'),0) AS DELIVERY_DATE,
               (SELECT CODE FROM STOCK00 S0 WHERE S0.CODE=BOM.BOMREC_SOURCECODE AND TRIM(S0.GK_7)='PAINTED') AS PAINTED,
               (SELECT CODE FROM STOCK00 S0 WHERE S0.CODE=BOM.BOMREC_SOURCECODE AND TRIM(S0.GK_13)='RAWM-1') AS RAWM-01,
               (SELECT CODE FROM STOCK00 S0 WHERE S0.CODE=BOM.BOMREC_SOURCECODE AND TRIM(S0.GK_16)='RAWM-02') AS RAWM-02
      
          FROM STOCK40T SL
          LEFT JOIN STOCK00 ST ON TRIM(ST.CODE)=TRIM(SL.CODE)
          LEFT JOIN CUSTOM00 C0 ON TRIM(C0.CODE)=TRIM(SL.CUSTOMERCODE)
          LEFT JOIN BOMU01T BOM ON BOM.BOMREC_CODE=SL.CODE)
 GROUP BY DOCUMENT_NO,
       CUSTOMER_CODE,
       CUSTOMER_NAME,
       PRODUCT_CODE,
       PRODUCT_NAME,
       ORDER_QUANTITY,
       SHIPPED_QUANTITY,
       OPEN_ORDER_QUANTITY,
       DELIVERY_DATE