Oracle 11g SQL需求：将单行列拆分为6行并自定义新增列

Got it, let's break this down using your Emp/Dept example—this makes it easy to map to your actual complex query later.

First, let's define the base join query that returns your 9 columns. For this example, we'll assume the first 3 columns are empno, ename, job, followed by the 6 columns you want to unpivot: mgr, hiredate, sal, deptno, dname, loc.

Step 1: Wrap Your Complex Join in a CTE #

First, we'll use a Common Table Expression (CTE) to encapsulate your multi-table join. This keeps the unpivot logic clean and easy to modify:

WITH base_data AS (
  -- Replace this with your actual complex multi-table join query
  SELECT 
    e.empno,
    e.ename,
    e.job,
    e.mgr,
    e.hiredate,
    e.sal,
    e.deptno,
    d.dname,
    d.loc
  FROM Emp e
  JOIN Dept d ON e.deptno = d.deptno
)

Step 2: Apply UNPIVOT to Split Rows #

Next, we'll use the UNPIVOT clause to convert the 6 columns into rows, with your custom labels for each row. A key thing to note: if the columns you're unpivoting have different data types (like dates, numbers, and strings), you'll need to cast them to a common type (e.g., VARCHAR2) to avoid errors.

Here's the full query:

WITH base_data AS (
  SELECT 
    e.empno,
    e.ename,
    e.job,
    e.mgr,
    e.hiredate,
    e.sal,
    e.deptno,
    d.dname,
    d.loc
  FROM Emp e
  JOIN Dept d ON e.deptno = d.deptno
)
SELECT 
  empno,
  ename,
  job,
  custom_label,
  corresponding_value
FROM base_data
UNPIVOT (
  corresponding_value FOR custom_label IN (
    -- Map each original column to your custom label, casting to consistent type if needed
    CAST(mgr AS VARCHAR2(20)) AS 'mgr',
    TO_CHAR(hiredate, 'YYYY-MM-DD') AS 'hiredate',
    CAST(sal AS VARCHAR2(20)) AS 'sal',
    CAST(deptno AS VARCHAR2(20)) AS 'deptno',
    dname AS 'dname',
    loc AS 'loc'
  )
);

How This Works #

• The UNPIVOT clause takes each of the 6 columns from base_data and turns them into individual rows.
• custom_label is the column that will hold your specified strings ('mgr', 'hiredate', etc.).
• corresponding_value is the column that holds the actual value from the original column (we cast non-string types to VARCHAR2 to ensure all values fit in the same column).
• The first 3 columns (empno, ename, job) stay the same across all 6 rows generated from each original row.

Example Output #

If your base query returns a row like this:

The unpivoted result will be 6 rows:

Adapting to Your Actual Query #

Just replace the base_data CTE with your real multi-table join. Make sure the first 3 columns are the ones you want to repeat, and map the remaining 6 columns to your custom labels in the UNPIVOT clause (adjusting casts as needed for your data types).

内容的提问来源于stack exchange，提问作者anjanb