Db2 Developer-C 11.1

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t (
  id int,
  first_name varchar(10),
  last_name varchar(10),
  preference varchar(10)
);
insert into t ( id, first_name, last_name, preference)
  values (1, 'John', 'Smith', 'Text');
insert into t ( id, first_name, last_name, preference)
  values (1, 'John', 'Smith', 'Mail');
insert into t ( id, first_name, last_name, preference)
  values (2, 'Anne', 'Doe', 'Twitter');
select 
  id,
  max(first_name) as first_name,
  max(last_name) as last_name,
  max(case when rn = 1 then preference end) as primary_method,
  max(case when rn = 2 then preference end) as secondary_method
from (
  select t.*, row_number() over(partition by id) as rn
  from t
) x
group by id;


ID
FIRST_NAME
LAST_NAME
PRIMARY_METHOD
SECONDARY_METHOD


1
John
Smith
Text
Mail

2
Anne
Doe
Twitter
null