오라클 - 행과 열을 변경하여 Select 하는 Query

ex)

   /* Formatted on 2018/09/25 오후 2:18:56 (QP5 v5.227.12220.39754) */
WITH LOT_ROW_COL_CHANGE
     AS (SELECT L.CARRIERNAME AS "CarrierName",
                SUBSTR (L.LOTNAME, 0, 8) AS "LotName",
                SUBSTR (L.LOTNAME, 9, 2) AS "SplitSeq",
                L.PRODUCTSPECNAME AS "ProductSpec",
                L.ECCODE AS "EC Code",
                L.PRODUCTREQUESTNAME AS "WorkOrder",
                PQ.DESCRIPTION AS "WorkOrderDesc",
                CASE WHEN L.PRODUCTREQUESTNAME = 'MIXED' THEN (SELECT TO_CHAR(WM_CONCAT(DISTINCT PR.PRODUCTREQUESTTYPE)) AS PRODUCTREQUESTTYPE FROM
                        PRODUCT P, PRODUCTREQUEST PR
                            WHERE P.LOTNAME = :LOTNAME
                            AND P.PRODUCTREQUESTNAME = PR.PRODUCTREQUESTNAME)
                ELSE PQ.PRODUCTREQUESTTYPE
                END AS "WorkOrderType",
                --PQ.PRODUCTREQUESTTYPE AS "WorkOrderType",
                L.LOTSTATE AS "LotState",
                L.LOTPROCESSSTATE AS "LotProcessState",
                L.LOTHOLDSTATE AS "LotHoldState",
                TO_CHAR (L.PRODUCTQUANTITY) AS "ProductQuantity",
                TO_CHAR (L.LOTGRADE) AS "LotGrade",
                TO_CHAR (
                   (SELECT COUNT (PRODUCTGRADE)
                      FROM PRODUCT
                     WHERE     1 = 1
                           AND LOTNAME = :LOTNAME
                           AND PRODUCTGRADE = 'S'
                           AND PRODUCTSTATE!='Scrapped'))
                   AS "S GlassCount",
                L.DEPARTMENTNAME AS "Deparment",
                L.SUPERLOTFLAG AS "SuperLotFlag",
                TO_CHAR (L.PRIORITY) AS "Priority"
           FROM (SELECT *
                   FROM LOT
                  WHERE 1 = 1 AND LOTNAME = :LOTNAME) L
                LEFT OUTER JOIN PRODUCTREQUEST PQ
                   ON L.PRODUCTREQUESTNAME = PQ.PRODUCTREQUESTNAME),
     TMP
     AS (SELECT 1 POSITION, 'CarrierName' COLUMN_NAME FROM DUAL
         UNION
         SELECT 2 POSITION, 'LotName' COLUMN_NAME FROM DUAL
         UNION
         SELECT 3 POSITION, 'SplitSeq' COLUMN_NAME FROM DUAL
         UNION
         SELECT 4 POSITION, 'ProductSpec' COLUMN_NAME FROM DUAL
         UNION
         SELECT 5 POSITION, 'EC Code' COLUMN_NAME FROM DUAL
         UNION
         SELECT 6 POSITION, 'WorkOrder' COLUMN_NAME FROM DUAL
         UNION
         SELECT 7 POSITION, 'WorkOrderDesc' COLUMN_NAME FROM DUAL
         UNION
         SELECT 8 POSITION, 'WorkOrderType' COLUMN_NAME FROM DUAL
         UNION
         SELECT 9 POSITION, 'LotState' COLUMN_NAME FROM DUAL
         UNION
         SELECT 10 POSITION, 'LotProcessState' COLUMN_NAME FROM DUAL
         UNION
         SELECT 11 POSITION, 'LotHoldState' COLUMN_NAME FROM DUAL
         UNION
         SELECT 12 POSITION, 'ProductQuantity' COLUMN_NAME FROM DUAL
         UNION
         SELECT 13 POSITION, 'LotGrade' COLUMN_NAME FROM DUAL
         UNION
         SELECT 14 POSITION, 'S GlassCount' COLUMN_NAME FROM DUAL
         UNION
         SELECT 15 POSITION, 'Deparment' COLUMN_NAME FROM DUAL
         UNION
         SELECT 16 POSITION, 'SuperLotFlag' COLUMN_NAME FROM DUAL
         UNION
         SELECT 17 POSITION, 'Priority' COLUMN_NAME FROM DUAL
         ORDER BY POSITION)
  SELECT A.COLUMN_NAME KEY, B.VALUE
    FROM TMP A
         LEFT OUTER JOIN
         (SELECT *
            FROM LOT_ROW_COL_CHANGE UNPIVOT (VALUE
                                    FOR KEY
                                    IN  ("CarrierName",
                                        "LotName",
                                        "SplitSeq",
                                        "ProductSpec",
                                        "EC Code",
                                        "WorkOrder",
                                        "WorkOrderDesc",
                                        "WorkOrderType",
                                        "LotState",
                                        "LotProcessState",
                                        "LotHoldState",
                                        "ProductQuantity",
                                        "LotGrade",
                                        "S GlassCount",
                                        "Deparment",
                                        "SuperLotFlag",
                                        "Priority"))) B
            ON A.COLUMN_NAME = B.KEY
ORDER BY A.POSITION