split data in a single column into multiple columns in oracle

Question

my_table :

Name	Value
item_1	AB
item_2	2
item_3	B1
item_1	CD
item_1	EF
item_2	3
item_3	B2
item_4	ZZ

required output:

item_1	item_2	item_3	item_4
AB	2	B1	ZZ
CD	3	B2	NULL
EF	NULL	NULL	NULL

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.

Answer 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