Left Join within the same table

[annick]

Hello,

I have a table with 3 columns : A,B,C.

Example:

A	B	C
101	X	1
102	Y	1
101		2
102		2

For each line with column C = 2, I would like to retrieve the line (in the same table) with the same value in column A and with column C = 1; Once the match is found, the value of Column B should be copied 

Example above would result in:

A	B	C
101	X	1
102	Y	1
101	X	2
102	Y	2

As I have a big volume of data, I would like an optimized way to do this operation and appreciate your advice.

Many thanks in advance.

Annick

[dplr-rn]

not sure if there are any fruther subtleties involved in your requriement but below approach should work for you

first load values where column b is not empty in a map

i.e.

BColMap:

mapping load

A,B from xyz where len(B)>0;

A	B
101	X
102	Y

then while loading your actual  table

load

A

,if(len(B)>0,B,applymap('BColMap',A,'')) as B

,C

from xyz;