Hi All,
I have table like below.
DR_NAME ACCT1 ACCT2 ACCT3 ACC4
======================================
SMITH 1234
SMITH 1234 1234
JONES 5678 2541 2547
JONES 5678 2541 0001
MARK NULL
WARD 8754 6547
I need out put should be like below.
DR_NAME ACCT
==============
SMITH 1234
SMITH 1234
SMITH 1234
JONES 5678
JONES 2541
JONES 2547
JONES 5678
JONES 2541
JONES 0001
MARK NULL
WARD 8754
WARD 6547
In google I found below query and I used but it is giving incorrect/duplicate data.
select t1.DR_NAME, d.Acct
from yourtable t1
left join
(
select DR_NAME, ACCT1 as Acct
from yourtable
where acct1 is not null
union all
select DR_NAME, ACCT2 as Acct
from yourtable
where acct2 is not null
union all
select DR_NAME, ACCT3 as Acct
from yourtable
where acct3 is not null
union all
select DR_NAME, ACCT4 as Acct
from yourtable
where acct4 is not null
) d
on t1.DR_NAME = d.DR_NAME;
Can any one please let me know how to do it.
Thanks,
Narasimha