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

Question

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

我希望运行一个过程来提取组织如下的数据：

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

并将其呈现在如下所示的表中：

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

从本质上讲，我有多个人的多个记录（最好通过电子邮件记录作为唯一标识符进行标识），我想为给定用户捕获这些多个首选项，并为每个用户（每封电子邮件）创建 1 条单独的记录。

Answer 1

如果您只有两个首选项，则可以使用 min() 和 max() ：

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

编辑：

如果最多有七个值，则使用 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;

编辑二：

您实际上可以使用相关的子查询而不是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;