All Forums Database
nani_hero 12 posts Joined 10/14
23 Nov 2014
need the query for the o/p.

hi
i have a table with data below and i want to display the result ass o/p below...thnks inadvance.
eid    ename     loc       flag
1      mahesh   ind       current
1      mahesh   usa      previous
 
o/p:
eid     ename   oldloc   newloc
1        mahesh  usa      ind
 

Raja_KT 1246 posts Joined 07/09
23 Nov 2014

Hw about your previous to previous? you can try this way:
select eid,
ename,
max(case when flag='previous' then loc end) over(partition by eid,ename  order eid,ename ) oldloc,
max(case when flag='current' then loc end) over(partition by eid,ename  order eid,ename ) newloc from youttable
qualify row_number() over(partition by partition by eid,ename  order eid,ename)=1
 

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.