需要将具有相同 ID 的多行数据转换为具有多列的 1 行 [英] need to convert data in multiple rows with same ID into 1 row with multiple columns

问题描述

我查看了已解决的问题的版本，但我发现的一些好的提示(例如，使用 rank() over (partition...))在我使用的 Sybase 版本中似乎不起作用.

I reviewed versions of my question already addressed, but some of the good tips I found (using rank() over (partition...) for example, do not seem to work in the Sybase version I am on.

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

I am hoping to run a procedure that pulls data organized as follows:

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

Email | Preference
email1 | PreferenceXYZ
email1 | PreferenceABC

并将其呈现在如下表中:

And render it in a table like the following:

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

Email | Preference1 | Preference2
email1 | PreferenceXYZ | PreferenceABC

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

In essence, I have multiple records for the same person (best identified via email record as a unique identifier) and I want to capture these multiple preferences for a given user and create 1 individual record per user (per email).

推荐答案

如果你只有两个首选项，那么你可以使用 min() 和 max():

If you only have two preferences, then you can use 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;

如果您有多达七个值，则使用 row_number() 进行透视:

If you have up to seven values, then pivot using 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() 来做到这一点:

You can actually do this with a correlated subquery instead of 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;

这篇关于需要将具有相同 ID 的多行数据转换为具有多列的 1 行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！