Columns With Multiple Unpivot or For

Question

I have the below table structure

CREATE TABLE test_unpivot
(
   prod_id              NUMBER,
   prod_name_reuse      VARCHAR2 (1),
   prod_cat_reuse       VARCHAR2 (1),
   prod_name_transfer   VARCHAR2 (1),
   prod_cat_transfer    VARCHAR2 (1),
   remarks              VARCHAR2 (10)
);

I am using unpivot as

    SELECT prod_id, value1, col1
  FROM test_unpivot2 UNPIVOT (value1
                     FOR col1
                     IN (prod_name_reuse, prod_cat_reuse))
 WHERE prod_id = 120

and gives the results as

+---------+--------+-----------------+
| PROD_ID | VALUE1 |      COL1       |
+---------+--------+-----------------+
|     120 | Y      | PROD_NAME_REUSE |
|     120 | N      | PROD_CAT_REUSE  |
+---------+--------+-----------------+

My question is using UNPIVOT, is it possible to achieve the below result? i.e. prod_name_transfer is another column and should appear as value2. Does UNPIVOT support multiple FOR..IN statement?

+---------+--------+-----------------+--------+---------------------+
| PROD_ID | VALUE1 |      COL1       |VALUE2 |      COL2            |
+---------+--------+-----------------+--------+---------------------+
|     120 | Y      | PROD_NAME_REUSE | Y      | PROD_NAME_TRANSFER  |
|     120 | N      | PROD_CAT_REUSE  | N      | PROD_NAME_TRANSFER  |
+---------+--------+-----------------+------------------------------+

Test table and data

My actual table has around 54 columns with many records.

Table structure

CREATE TABLE test_unpivot2
(
   prod_id              NUMBER,
   prod_name_reuse      VARCHAR2 (1),
   prod_cat_reuse       VARCHAR2 (1),
   prod_name_transfer   VARCHAR2 (1),
   prod_cat_transfer    VARCHAR2 (1),
   remarks              VARCHAR2 (10)
);

Table data

INSERT INTO test_unpivot2
     VALUES (120,
             'Y',
             'N',
             'Y',
             'N',
             'Test1');

INSERT INTO test_unpivot2
     VALUES (121,
             'Y',
             'N',
             'Y',
             'N',
             'Test2');

INSERT INTO test_unpivot2
     VALUES (122,
             'Y',
             'N',
             'Y',
             'N',
             'Test3');

INSERT INTO test_unpivot2
     VALUES (123,
             'Y',
             'N',
             'Y',
             'N',
             'Test4');

COMMIT

Answer 1

Do you even need to use UNPIVOT?

SELECT id,
       prod_name_reuse      AS value1,
       'PROD_NAME_REUSE'    AS col1,
       prod_name_transfer   AS value2,
       'PROD_NAME_TRANSFER' AS col2
FROM   test_unpivot
WHERE  id = 120
UNION ALL
SELECT id,
       prod_cat_reuse      AS value1,
       'PROD_CAT_REUSE'    AS col1,
       prod_cat_transfer   AS value2,
       'PROD_CAT_TRANSFER' AS col2
FROM   test_unpivot
WHERE  id = 120;

Update:

SELECT *
FROM   (
  SELECT u.*,
         REGEXP_SUBSTR( type, '_(.+?)_', 1, 1, NULL, 1 ) AS namecat,
         REGEXP_SUBSTR( type, '[^_]+$' ) AS reusetransfer
  FROM   testunpivot
  UNPIVOT (
    value
    FOR type IN (
      prod_name_reuse,
      prod_cat_reuse,
      prod_name_transfer,
      prod_cat_transfer
    )
  ) u
)
PIVOT (
  MAX( value ) AS value,
  MAX( type  ) AS col
  FOR reusetransfer IN ( 'REUSE', 'TRANSFER' )
);