Transpose and insert multiple rows with differing values

Question

I have a more complicated version of the following table:

ID | FIRST  | LAST     | EMAIL
1  | John   | Doe      | jdoe@example.com
1  | Mack   | Johnson  | mjohnson@example.com
1  | Steven | Michaels | smichaels@example.com
2  | Sarah  | Sampson  | ssampson@example.com
2  | Tom    | Smith    | tsmith@example.com
2  | Jane   | Rogers   | jrogers@example.com
3  | Bob    | Johns    | bjohns@example.com
3  | Kim    | Lane     | klane@example.com
3  | Ron    | Swanson  | rswanson@example.com

and I would like to write one query that inserts this data into another table to look like this (the table already exists):

ID | first1 | last1    | email1               | first2 | last2    | email2               | first3 | last3    | email3 
1  | John   | Doe      | jdoe@example.com     | Mack   | Johnson  | mjohnson@example.com | Steven | Michaels | smichaels@example.com
2  | Sarah  | Sampson  | ssampson@example.com | Tom    | Smith    | tsmith@example.com   | Jane   | Rogers      | jrogers@example.com
3  | Bob    | Johns    | bjohns@example.com   | Kim    | Lane     | klane@example.com    | Ron    | Swanson     | rswanson@example.com

I feel that this should be easy but the concept is eluding me. What is best practice for accomplishing this?

Maybe I should also mention that I have written a function that I can pass the ID, an index number, and a column name to return a value (i.e. getpersoninfo(2,'1','first') to return Sarah).

select a_id,
  FIRST1, LAST1, EMAIL1,
  FIRST2, LAST2, EMAIL2,
  FIRST3, LAST3, EMAIL3
from
(
  select a_id, col||rn as new_col, value
  from
  (
    select a_id, first_name, last_name, email,
      cast(row_number() over(partition by a_id order by a_id) as varchar2(10)) rn
    from dump_recs_2015
  ) 
  unpivot
  (
    value
    for col in (first_name, last_name, email)
  )
) 
pivot
(
  max(value)
  for new_col in ('FIRST1' FIRST1, 'LAST1' LAST1, 'EMAIL1' EMAIL1,
                  'FIRST2' FIRST2, 'LAST2' LAST2, 'EMAIL2' EMAIL2,
                  'FIRST3' FIRST3, 'LAST3' LAST3, 'EMAIL3' EMAIL3)
);

Answer 1

Since you are using Oracle 11g, you can implement both the UNPIVOT and PIVOT functions.

The UNPIVOT will take your columns first, last and email and convert the values into rows. Then you can PIVOT the new names First1, etc into columns:

select id,
  First1, Last1, Email1,
  First2, Last2, Email2,
  First3, Last3, Email3
from
(
  select id, col||rn as new_col, value
  from
  (
    select id, first, last, email,
      cast(row_number() over(partition by id order by id) as varchar2(10)) rn
    from yourtable
  ) 
  unpivot
  (
    value
    for col in (first, last, email)
  )
) 
pivot
(
  max(value)
  for new_col in ('FIRST1' First1, 'LAST1' Last1, 'EMAIL1' Email1,
                  'FIRST2' First2, 'LAST2' Last2, 'EMAIL2' Email2,
                  'FIRST3' First3, 'LAST3' Last3, 'EMAIL3' Email3)
) 

See SQL Fiddle with Demo. This can then be used in an INSERT statement to load the data into a new table.

Edit, based on your column name changes, you will use:

select a_id,
  First1, Last1, Email1,
  First2, Last2, Email2,
  First3, Last3, Email3
from
(
  select a_id, col||rn as new_col, value
  from
  (
    select a_id, first_name, last_name, email,
      cast(row_number() over(partition by a_id order by a_id) as varchar2(10)) rn
    from yourtable
  ) 
  unpivot
  (
    value
    for col in (first_name, last_name, email)
  )
) 
pivot
(
  max(value)
  for new_col in ('FIRST_NAME1' First1, 'LAST_NAME1' Last1, 'EMAIL1' Email1,
                  'FIRST_NAME2' First2, 'LAST_NAME2' Last2, 'EMAIL2' Email2,
                  'FIRST_NAME3' First3, 'LAST_NAME3' Last3, 'EMAIL3' Email3)
) 

See SQL Fiddle with Demo