Adhoc SQL request

Database
KVB
N/A

Adhoc SQL request

3 Replies Latest reply: Jul 17, 2013 1:44 PM by KVB RSS

<:header class=js-original-header>

Adhoc sql request

KVB Explorer

<:section class=j-original-message>
Currently Being Moderated


Hi

 

I have a requirement like

 

CREATE TABLE DX(LEVEL0 CHAR(1),LEVEL1 CHAR(1),LEVEL2 CHAR(1),LEVEL3 CHAR(1));

SEL * FROM DX

INSERT INTO DX VALUES('A','A',NULL,NULL);

INSERT INTO DX VALUES('B',NULL,NULL,NULL);

INSERT INTO DX VALUES('C','C','C',NULL);

INSERT INTO DX VALUES('D',NULL,NULL,NULL);

INSERT INTO DX VALUES('E','E',NULL,NULL);

INSERT INTO DX VALUES('F','F','F','F');

 

 

LEVEL0 LEVEL1 LEVEL2 LEVEL3

A A ? ?

B ? ? ?

C C C ?

D ? ? ?

E E ? ?

F F F F

     data indicates the jobname.If you observe,it is active only to the particular level.I need my output in such a way that it should give the jobname of the maxlevel for each job.

My output should like

 

 

LEVEL0 LEVEL1 LEVEL2 LEVEL3
B A C F
D E    

 



3 REPLIES
KVB
N/A

Re: Adhoc SQL request

My output should look like

Level0    Level1    Level2    Level3

B               a            c            f

d                e

N/A

Re: Adhoc SQL request

This could do the job:

select level0.level0, level1.level1, level2.level2, level3.level3
from (
select
row_number() over ( order by level0 ) as rownum,
level0
from dx
where level0 is not null and level1 is null
) level0
full outer join (
select
row_number() over ( order by level1 ) as rownum,
level1
from dx
where level1 is not null and level2 is null
) level1
on level1.rownum = level0.rownum
full outer join (
select
row_number() over ( order by level1 ) as rownum,
level2
from dx
where level2 is not null and level3 is null
) level2
on level2.rownum = coalesce( level0.rownum, level1.rownum )
full outer join (
select
row_number() over ( order by level1 ) as rownum,
level3
from dx
where level3 is not null
) level3
on level3.rownum = coalesce( level0.rownum, level1.rownum, level2.rownum )
order by coalesce( level0.rownum, level1.rownum, level2.rownum, level3.rownum );
KVB
N/A

Re: Adhoc SQL request

Thanks Muthm.It helped a lot.