Oracle pivoting for text issue

Question

ColumnA	ColumnB	Codes	ColumnD	ColumnE
1	A	Type1	Text1	Yes
1	A	Type1	Text2	Yes
1	B	Type2	Text3	Yes
2	C	Type1	Text4	No
2	D	Type2	Text5	No
2	E	Type3	Text6	No

I want to be able to pivot on Column B to have the view look something like the below:

ColumnA	Type1 - ColumnB	Type2 - ColumnB	Type3 - ColumnB	Type2 - ColumnD	Type2 - ColumnD	Type3 - ColumnD	Column E
1	A	B	NA	Text1 & Text2	Text3	NA	Yes
2	C	D	E	Text4	Text5	Text6	No

Pleas note column E is associated with Column A so can only 1 value for each recording Column A

Answer 1

Use the DENSE_RANK function to number the rows according to the columnb values for each columna and then PIVOT:

SELECT columna,
       "1_TYPE_B" AS type_1_b,
       "2_TYPE_B" AS type_2_b,
       "3_TYPE_B" AS type_3_b,
       "1_TYPE_D" AS type_1_d,
       "2_TYPE_D" AS type_2_d,
       "3_TYPE_D" AS type_3_d,
       columne
FROM   (
  SELECT ColumnA, ColumnB, ColumnD, ColumnE,
         DENSE_RANK() OVER (PARTITION BY columnA ORDER BY columnB) AS rnk
  FROM   table_name t
)
PIVOT (
  MAX(columnB) AS type_b,
  LISTAGG(columnD, ' & ') WITHIN GROUP (ORDER BY columnD) AS type_d
  FOR rnk IN (
    1 AS "1", 2 AS "2", 3 AS "3"
  )
);

Which, for the sample data:

CREATE TABLE table_name (ColumnA, ColumnB, Codes, ColumnD, ColumnE) AS
SELECT 1, 'A', 'Type1', 'Text1', 'Yes' FROM DUAL UNION ALL
SELECT 1, 'A', 'Type1', 'Text2', 'Yes' FROM DUAL UNION ALL
SELECT 1, 'B', 'Type2', 'Text3', 'Yes' FROM DUAL UNION ALL
SELECT 2, 'C', 'Type1', 'Text4', 'No'  FROM DUAL UNION ALL
SELECT 2, 'D', 'Type2', 'Text5', 'No'  FROM DUAL UNION ALL
SELECT 2, 'E', 'Type3', 'Text6', 'No'  FROM DUAL;

Outputs:

COLUMNA	TYPE_1_B	TYPE_2_B	TYPE_3_B	TYPE_1_D	TYPE_2_D	TYPE_3_D	COLUMNE
1	A	B	null	Text1 & Text2	Text3	null	Yes
2	C	D	E	Text4	Text5	Text6	No

fiddle