Store row values as new column before group by

Question

Anyone know how I can group by, but keep a distinct column? I messed around with UNPIVOT but couldnt find a good explanation to help me write my code. Anyone have a solution?

I have data that looks like this:

acct contact email phone
1     44      abc  NULL
1     33      NULL 123
2     2       xxx  NULL
2     22      NULL 456

I use

select acct,max(email) as email, max(phone) as phone from my_table 
group by acct

to wind up with this:

acct email phone
1    abc 123
2    xxx  456

but my end goal is to group by and create a separate contact column for phone and email like this:

acct contact_email email phone contact_phone
1     44           abc  123    33
2     2            xxx  456    22

Is it possible for there to be more than 2 records per account? As that would trip up your scheme a bit. — Jeroen Mostert, Jul 25 '18 at 14:13
Actually it is the n-per-group question. Look at https://stackoverflow.com/questions/7745609/sql-select-only-rows-with-max-value-on-a-column — Akmal Salikhov, Jul 25 '18 at 14:14

score 2 · Accepted Answer · answered Jul 25 '18 at 14:26

You appears to want simple aggregation :

select acct, 
       max(case when phone is null then contact end) as contact_email,
       max(email) as email, max(phone) as phone, 
       max(case when email is null then contact end) as contact_phone
from table t
group by acct;

Gulam Husain Ansari · Answer 2 · 2018-07-25T14:47:08.100

0

Try Below, might be it will help, without sub query and without group

SELECT t1.acct,t1.contact contact_email,t1.email,  t2.phone,t2.contact contact_phone  FROM my_table t1 inner join my_table t2 on t1.acct=t2.acct
 where t1.email is not null and t2.phone is not null

edited Jul 25 '18 at 14:47

answered Jul 25 '18 at 14:34

Gulam Husain Ansari

165
1
13

you can do this in better way by usiing temp table and use the procedure for call. above one is just Idea – Gulam Husain Ansari Jul 25 '18 at 14:35

Store row values as new column before group by

2 Answers2