需要将具有相同ID的多行数据转换为1行多列数据

我回顾了已经解决的问题的版本，但是我发现的一些好的技巧（例如，在（分区...）上使用rank（））似乎不适用于我所在的Sybase版本。

我希望运行一个程序来提取按如下方式组织的数据：

电子邮件 |偏爱
电子邮件1 |偏好XYZ
电子邮件1 |偏好ABC

并将其呈现在如下表中：

电子邮件 |偏好1 |偏好2
电子邮件1 |偏好XYZ |偏好ABC

本质上，我有同一个人的多个记录（最好通过电子邮件记录作为唯一标识符来识别），并且我想捕获给定用户的这些多个偏好并为每个用户（每封电子邮件）创建 1 个单独的记录。

如果您只有两个偏好，那么您可以使用min() and max():

select email, min(preference) as preference1,
       (case when min(preference) <> max(preference) then max(preference) end) as preference2
from t
group by email;

EDIT:

如果最多有七个值，则使用row_number():

select email,
       max(case when seqnum = 1 then preference end) as preference1,
       max(case when seqnum = 2 then preference end) as preference2,
       max(case when seqnum = 3 then preference end) as preference3,
       max(case when seqnum = 4 then preference end) as preference4,
       max(case when seqnum = 5 then preference end) as preference5,
       max(case when seqnum = 6 then preference end) as preference6,
       max(case when seqnum = 7 then preference end) as preference7
from (select t.*, row_number() over (partition by email order by preference) as seqnum
      from t
     ) t
group by email;

EDIT II:

实际上，您可以使用相关子查询来做到这一点，而不是row_number():

select email,
       max(case when seqnum = 1 then preference end) as preference1,
       max(case when seqnum = 2 then preference end) as preference2,
       max(case when seqnum = 3 then preference end) as preference3,
       max(case when seqnum = 4 then preference end) as preference4,
       max(case when seqnum = 5 then preference end) as preference5,
       max(case when seqnum = 6 then preference end) as preference6,
       max(case when seqnum = 7 then preference end) as preference7
from (select t.*,
             (select count(*)
              from t t2
              where t2.email = t.email and
                    t2.preference <= t.preference
             ) as seqnum
      from t
     ) t
group by email;