Same value across multiple columns

Database
Enthusiast

Same value across multiple columns

Table structure as below:

 

EmpID ItemsPurchased EmailID

C12      Bread                 abc.xyz@gmail.com

C12      Milk 

C12      Butter

C14      Jam

C14      Choc

C15      Watch               gfx.rfg@yahoo.com

 

I would need email ids to populated partitioned by empid. ie., abc.xyz@gmail.com should be populated for all rows of C12 and similiarly, gfx.rfg@yahoo.com for C14 empid.

4 REPLIES
Highlighted
Junior Contributor

Re: Same value across multiple columns

Why should gfx.rfg@yahoo.com be assigned to C14, it's the email of C15?

Enthusiast

Re: Same value across multiple columns

Am sorry, its C14  only - typo.. 

 

We have only c12 and c14 here.

Teradata Employee

Re: Same value across multiple columns

You could use the MAX window function :

select EmpID
     , ItemsPurchased
     , EmailID
     , max(EmailID) over(partition by EmpId) as EmailID_populated
  from MyTable;

Re: Same value across multiple columns

You can use this sql.

 

sel empid,
last_value(emailid ignore nulls ) over (partition by empid )
from table ;