In Oracle, how do I pivot data into multiple columns?

I have the following test table and I need to get the following result table. I've tried a pivot, but that only seems to work when you have a descriptor and a value. In this case, we just have a value. Some site_id's might only have 1 value, but some can have up to 3 other site_id_alt's. There are over 2800 unique site_id's, so manually coding them into a pivot function an option.

I was thinking some sort of partition might work, but no idea where to start with that.

drop table site_id_cd_test;
create table site_id_cd_test
 (
  site_id_alt VARCHAR(30),
  site_id VARCHAR(30)
);

insert into site_id_cd_test values('1A1-071', '1A1-071');
insert into site_id_cd_test values('1A1-071O', '1A1-071');
insert into site_id_cd_test values('030256', '1A1-071');
insert into site_id_cd_test values('04268384', '1A1-071');
insert into site_id_cd_test values('04268383', '1A1-072');


drop table site_id_cd_result;
create table site_id_cd_result
 (
  site_id VARCHAR(30),
  site_id_alt_01 VARCHAR(30),
  site_id_alt_02 VARCHAR(30),
  site_id_alt_03 VARCHAR(30)
);

insert into site_id_cd_result values('1A1-071', '1A1-071O', '030256', '04268384' );
insert into site_id_cd_result values('1A1-072', '04268383', NULL, NULL);

EDIT 9_19_19:

After feedback from @MT0, we get about 90% of the way there (it pivots well on row number). When the site_id matches the site_id_alt, then it adds those as a new row (as shown below in an example table). Ideally we would like to have the matching site_id and site_id_alt in the same row with the other values.

Summary of the below example tables:

site_id_cd_result is the resulting table when running with the row number pivoting

site_id_cd_result_02 is the resulting table when using site_id <> site_id_alt

drop table site_id_cd_result;

create table site_id_cd_result
 (
  site_id VARCHAR(30),
  site_id_alt_01 VARCHAR(30),
  site_id_alt_02 VARCHAR(30),
  site_id_alt_03 VARCHAR(30),
 site_id_alt_04 VARCHAR(30)

);

insert into site_id_cd_result values('1A1-071', '1A1-071', NULL, NULL, NULL);
insert into site_id_cd_result values('1A1-071', NULL, '040777', '04253626', '1A1-071O');
insert into site_id_cd_result values('1A1-072', '04268383', '123546', NULL, NULL);


drop table site_id_cd_result_02;

create table site_id_cd_result_02
 (
  site_id VARCHAR(30),
  site_id_alt_01 VARCHAR(30),
  site_id_alt_02 VARCHAR(30),
  site_id_alt_03 VARCHAR(30),
 site_id_alt_04 VARCHAR(30)

);

insert into site_id_cd_result_02 values('1A1-071', '040777', '04253626', '1A1-071O', NULL);
insert into site_id_cd_result_02 values('1A1-072', '04268383', '123546', NULL, NULL);

Use the ROW_NUMBER() analytic function to give each SITE_ID_ALT a column number for each SITE_ID and then you can PIVOT on that:

Query:

INSERT INTO site_id_cd_result ( site_id, site_id_alt_01, site_id_alt_02, site_id_alt_03 )
SELECT site_id,
       site_id_alt_01,
       site_id_alt_02,
       site_id_alt_03
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER ( PARTITION BY site_id ORDER BY ROWNUM ) AS rn
  FROM   site_id_cd_test t
  -- WHERE site_id <> site_id_alt    -- If you don't want the matching value
)
PIVOT ( MAX( site_id_alt ) FOR rn IN (
  1 AS site_id_alt_01,
  2 AS site_id_alt_02,
  3 AS site_id_alt_03
) )

Result:

SELECT * FROM site_id_cd_result;

SITE_ID | SITE_ID_ALT_01 | SITE_ID_ALT_02 | SITE_ID_ALT_03
:------ | :------------- | :------------- | :-------------
1A1-071 | 1A1-071        | 1A1-071O       | 030256        
1A1-072 | 04268383       | null           | null          

db<>fiddle here