need to convert data in multiple rows with same ID into 1 row with multiple columns

Question

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:

Email | Preference
email1 | PreferenceXYZ
email1 | PreferenceABC

And render it in a table like the following:

Email | Preference1 | Preference2
email1 | PreferenceXYZ | PreferenceABC

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).

Kohler

Answer 1

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;

EDIT:

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;

EDIT II:

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;

Knowling

Hello -- great idea. I could utilize this method for 75% of the records, but ~25% have up to 7 preferences. So 7 would be my upper limit. – Kohler

Thanks again -- unfortunately I receive Error (156) Incorrect syntax near the keyword 'over'. I have tried various things like this. I wonder if row_number() is also not supported. Thanks for the help....analyst relatively new to sybase here. – Kohler

You should tag your question with the version of Sybase you are using. – Knowling

Thank you -- I should have done that to begin with. Adaptive Server Enterprise/15.0.3/EBF 20621 ESD#4.2/P/RS6000/AIX 5.3/ase1503/2814/64-bit/FBO/Sat Dec 8 09:18:36 2012 – Kohler

Thank you very much Gordon -- that works spendidly. I realized that I need to include a date field, and choose the timestamp of the record that is the "highest" of the up to 7 preferences and include in the row --> email, date, pref1, pref2, pref3 etc. Thanks! – Kohler

@Kohler . . . Then instead of using preference for the comparison for seqnum, use timestamp. – Knowling

Thanks – I am actually away from my computer where I can test this out, but the main wrinkle here is that in some cases multiple records with the same preference have the same time stamp (to the minute). Would it assign an identical sequence number to two records with the same preference, and the same time stamp? Since I’m not at my normal comp I’m writing out the below from memory…but last night I added the line below under the first select statement to pull the most recent date. It seemed to work, but I'll test this more. select EmailAddress, max(CreationDate) AS CreationDateMAX – Kohler