Convert Rows to Columns in one SQL but not impact number rows

Question

I have a table its structure and data look like this:

then I want a SQL to convert it like this:

I really don't know how to write a SQL to accomplish this function, can anyone help me? I have referenced a lot of previous answers for this kind of topic but I cannot find one for my case. can anyone help me, please.

Answer 1

You can do it with a hierarchical query if you only have three levels to consider:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE table_name ( LineItem_Name, LineItem_Id, parent_id, dept_name, product_name ) AS
  SELECT 'ABC', 1, NULL, 'D1', 'P1' FROM DUAL UNION ALL
  SELECT 'CDF', 2,    1, 'D2', 'P2' FROM DUAL UNION ALL
  SELECT 'EFG', 3,    1, 'D3', 'P3' FROM DUAL UNION ALL
  SELECT 'HIJ', 4,    2, 'D4', 'P4' FROM DUAL;

Query 1:

SELECT CONNECT_BY_ROOT( LineItem_Name) AS LineItem_Level1,
       CASE LEVEL
       WHEN 3 THEN PRIOR LineItem_Name
       WHEN 2 THEN LineItem_Name
       END AS LineItem_Level2,
       CASE LEVEL
       WHEN 3 THEN LineItem_Name
       END AS LineItem_Level3,
       dept_name,
       product_name
FROM   table_name
START WITH parent_id IS NULL
CONNECT BY PRIOR LineItem_ID = parent_id

Results:

| LINEITEM_LEVEL1 | LINEITEM_LEVEL2 | LINEITEM_LEVEL3 | DEPT_NAME | PRODUCT_NAME |
|-----------------|-----------------|-----------------|-----------|--------------|
|             ABC |          (null) |          (null) |        D1 |           P1 |
|             ABC |             CDF |          (null) |        D2 |           P2 |
|             ABC |             CDF |             HIJ |        D4 |           P4 |
|             ABC |             EFG |          (null) |        D3 |           P3 |

Query 2: This is an alternative using recursive sub-query factoring which will get the grandparent and parent of the current line item; which is slightly different to the previous query but for 3 levels would give you the same result.

WITH tree ( id, grandparent, parent, item, dept_name, product_name ) AS (
  SELECT LineItem_id,
         NULL,
         NULL,
         LineItem_name,
         dept_name,
         product_name
  FROM   table_name
  WHERE  parent_id IS NULL
UNION ALL
  SELECT t.lineItem_id,
         p.parent,
         p.item,
         t.lineItem_name,
         t.dept_name,
         t.product_name
  FROM   tree p
         INNER JOIN
         table_name t
         ON ( p.id = t.parent_id )
)
SELECT COALESCE( grandparent, parent, item ) AS LineItem_Level1,
       CASE
       WHEN parent IS NULL THEN NULL
       WHEN grandparent IS NULL THEN item
       ELSE parent
       END AS LineItem_Level2,
       NVL2( grandparent, item, NULL ) AS LineItem_Level3,
       dept_name,
       product_name
FROM   tree

Results:

| LINEITEM_LEVEL1 | LINEITEM_LEVEL2 | LINEITEM_LEVEL3 | DEPT_NAME | PRODUCT_NAME |
|-----------------|-----------------|-----------------|-----------|--------------|
|             ABC |          (null) |          (null) |        D1 |           P1 |
|             ABC |             CDF |          (null) |        D2 |           P2 |
|             ABC |             EFG |          (null) |        D3 |           P3 |
|             ABC |             CDF |             HIJ |        D4 |           P4 |