Oracle SQL - Transpose Column to Row Grouping

Question

I have the following SQL query running against an Oracle 11g instance that works great:

SELECT
    TRIM(PART_NM) || TRIM(PART_NO),
    TRIM(MKBY_IND),
    MFG_TYP

FROM
    TblPARTS

WHERE
    ORDER_START_DATE >= (SYSDATE - 365)
    AND
    TO_CHAR(TRIM(PART_NO)) != '99'

Which yields results like the following:

| Part | Make / Buy |    Area   |
|:----:|:----------:|:---------:|
| AU54 |      M     |    Demo   |
| ZA45 |      M     | Construct |
| AD28 |      B     | Construct |
| SX73 |      B     | Construct |
| MO47 |      M     | Construct |
| IN66 |      B     | Construct |
| RP42 |      M     |    Demo   |
| HF77 |      B     |    Demo   |
| IE43 |      B     |  Personal |
| OX11 |      B     |  Personal |
| ZE45 |      M     |    Demo   |
| JH21 |      M     | Construct |
| AM91 |      M     | Construct |
| DS50 |      M     |    Demo   |
| LE29 |      M     |    Demo   |
| IP91 |      M     |    Demo   |
| ID42 |      M     |    Demo   |
| RI96 |      M     |    Demo   |

I have no problems there. However, it has been requested for me to transpose the Area column and instead group the rows by it. Something akin to:

|    Part   | Make / Buy |
|:---------:|:----------:|
| CONSTRUCT |  CONSTRUCT |
|    AU54   |      M     |
|    ZA45   |      M     |
|    AD28   |      B     |
|    SX73   |      M     |
|    MO47   |      M     |
|    IN66   |      B     |
|    JH21   |      M     |
|    AM91   |      B     |
|    DEMO   |    DEMO    |
|    RP42   |      M     |
|    HF77   |      M     |
|    ZE45   |      M     |
|    DS50   |      B     |
|    LE29   |      M     |
|    IP91   |      M     |
|    ID42   |      M     |
|    RI96   |      M     |
|  PERSONAL |  PERSONAL  |
|    IE43   |      M     |
|    OX11   |      M     |

In short, I am looking to find the unique values in the "Area" column (MFG_TYP) field, and to be able to group by them in a row. It should be noted that the three entries listed are valid in my dataset, but are subject to change, so I am not looking for a static solution, but instead a way to dynamically apply the transformation across all unique values in the Area column.

I have tried looking at these resource's but currently do not understand how to build a solution. Any advice is appreciated.

• SQL Transpose rows to columns (group by key variable)?
• Transpose / Pivot distinct row attribute as column and group another attribute?
• https://www.sqlshack.com/multiple-options-to-transposing-rows-into-columns/

Answer 1

Quick solution with ROLLUP Extension to GROUP BY:

with
  TblPARTS(Part, Make_Buy, Area) as (
    select 'AU54', 'M', 'Demo' from dual union all
    select 'ZA45', 'M', 'Construct' from dual union all
    select 'AD28', 'B', 'Construct' from dual union all
    select 'SX73', 'B', 'Construct' from dual union all
    select 'MO47', 'M', 'Construct' from dual union all
    select 'IN66', 'B', 'Construct' from dual union all
    select 'RP42', 'M', 'Demo' from dual union all
    select 'HF77', 'B', 'Demo' from dual union all
    select 'IE43', 'B', 'Personal' from dual union all
    select 'OX11', 'B', 'Personal' from dual union all
    select 'ZE45', 'M', 'Demo' from dual union all
    select 'JH21', 'M', 'Construct' from dual union all
    select 'AM91', 'M', 'Construct' from dual union all
    select 'DS50', 'M', 'Demo' from dual union all
    select 'LE29', 'M', 'Demo' from dual union all
    select 'IP91', 'M', 'Demo' from dual union all
    select 'ID42', 'M', 'Demo' from dual union all
    select 'RI96', 'M', 'Demo' from dual union all
    select 'AO42', 'M', 'Public' from dual union all
    select 'OA42', 'B', 'Public' from dual
  ),
  a as (
    select
      Part, Make_Buy, Area,
      GROUPING_ID(Area, Part, Make_Buy) gid
    from TblPARTS
    group by rollup(Area, (Part, Make_Buy))
    having GROUPING_ID(Area, Part, Make_Buy) < 7
  )
select
  coalesce(Part, Area) Part,
  coalesce(Make_Buy, Area) Make_Buy
from a
order by Area, gid desc

Output:

+------------+-----------+
|    PART    |  MAKE_BUY |
+------------+-----------+
| Construct  | Construct |
| AM91       | M         |
| IN66       | B         |
| JH21       | M         |
| SX73       | B         |
| ZA45       | M         |
| AD28       | B         |
| MO47       | M         |
| Demo       | Demo      |
| ZE45       | M         |
| RP42       | M         |
| RI96       | M         |
| LE29       | M         |
| IP91       | M         |
| ID42       | M         |
| HF77       | B         |
| AU54       | M         |
| DS50       | M         |
| Personal   | Personal  |
| OX11       | B         |
| IE43       | B         |
| Public     | Public    |
| OA42       | B         |
| AO42       | M         |
+------------+-----------+

Test it online with Rextester.

Answer 2

It looks like this is what you're looking for:

SELECT
    'CONSTRUCT' AS "Part",
    'CONSTRUCT' AS "Make / Buy"
FROM DUAL

UNION

SELECT
    TRIM(PART_NM) || TRIM(PART_NO) AS "Part",
    TRIM(MKBY_IND) AS "Make / Buy"
FROM
    TblPARTS
WHERE
    ORDER_START_DATE >= (SYSDATE - 365)
    AND    TO_CHAR(TRIM(PART_NO)) != '99'
    AND    MFG_TYP = 'Construct'

UNION

SELECT
    'DEMO' AS "Part",
    'DEMO' AS "Make / Buy"
FROM DUAL

UNION

SELECT
    TRIM(PART_NM) || TRIM(PART_NO) AS "Part",
    TRIM(MKBY_IND) AS "Make / Buy"
FROM
    TblPARTS
WHERE
    ORDER_START_DATE >= (SYSDATE - 365)
    AND    TO_CHAR(TRIM(PART_NO)) != '99'
    AND    MFG_TYP = 'Demo'

UNION

SELECT
    'PERSONAL ' AS "Part",
    'PERSONAL ' AS "Make / Buy"
FROM DUAL

UNION

SELECT
    TRIM(PART_NM) || TRIM(PART_NO)) AS "Part",
    TRIM(MKBY_IND) AS "Make / Buy"
FROM
    TblPARTS
WHERE
    ORDER_START_DATE >= (SYSDATE - 365)
    AND    TO_CHAR(TRIM(PART_NO)) != '99'
    AND    MFG_TYP = 'Personal'