split data in a single column into multiple columns in oracle

my_table :

required output:

SQL query :

with item_1 as (select value from my_table where name  = 'item_1'),
item_2 as (select value from my_table where name  = 'item_2'),
item_3 as (select value from my_table where name  = 'item_3'),
item_4 as (select value from my_table where name  = 'item_4')
select item_1.value, item_2.value,item_3.value, item_4.value from item_1 cross join item_2 cross join item_3 cross join item_4;

If I am using pivot along with MAX aggregate function, the query will display only max values of the corresponding items instead of displaying all the values.

Is there any way to split a single column into multiple columns(using where condition as mentioned in the above query) without cross join.

Solution 1:

Use ROW_NUMBER and then PIVOT:

SELECT item_1,
       item_2,
       item_3,
       item_4
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (PARTITION BY name ORDER BY ROWNUM) AS rn
  FROM   table_name t
)
PIVOT (
  MAX(value) FOR name IN (
    'item_1' AS item_1,
    'item_2' AS item_2,
    'item_3' AS item_3,
    'item_4' AS item_4
  )
)

Which, for the sample data:

CREATE TABLE table_name (Name, Value) AS
SELECT 'item_1', 'AB' FROM DUAL UNION ALL
SELECT 'item_2', '2'  FROM DUAL UNION ALL
SELECT 'item_3', 'B1' FROM DUAL UNION ALL
SELECT 'item_1', 'CD' FROM DUAL UNION ALL
SELECT 'item_1', 'EF' FROM DUAL UNION ALL
SELECT 'item_2', '3'  FROM DUAL UNION ALL
SELECT 'item_3', 'B2' FROM DUAL UNION ALL
SELECT 'item_4', 'ZZ' FROM DUAL;

Outputs:

ITEM_1	ITEM_2	ITEM_3	ITEM_4
AB	2	B1	ZZ
CD	3	B2	null
EF	null	null	null

db<>fiddle here

Solution 2:

How about this?

DF column is calculated by row_number analytic function which partitions by each name (and sorts by value). It is ignored from the final column list, but its role is crucial in the GROUP BY clause.

SQL> with test (name, value) as
  2    (select 'item_1', 'AB' from dual union all
  3     select 'item_2', '2'  from dual union all
  4     select 'item_3', 'B1' from dual union all
  5     select 'item_1', 'CD' from dual union all
  6     select 'item_1', 'EF' from dual union all
  7     select 'item_2', '3'  from dual union all
  8     select 'item_3', 'B2' from dual union all
  9     select 'item_4', 'ZZ' from dual
 10    ),

 11  temp as
 12    (select name, value,
 13       row_number() over (partition by name order by value) df
 14     from test
 15    )
 16  select
 17    max(case when name = 'item_1' then value end) item_1,
 18    max(case when name = 'item_2' then value end) item_2,
 19    max(case when name = 'item_3' then value end) item_3,
 20    max(case when name = 'item_4' then value end) item_4
 21  from temp
 22  group by df;

ITEM_1 ITEM_2 ITEM_3 ITEM_4
------ ------ ------ ------
AB     2      B1     ZZ
CD     3      B2
EF

SQL>