SQL & PL/SQL

Hi,

What logic did you applied to remove the NULL values manually??


Thanks,
Avinash

No, I am not sure how to do it.

Try this:
Select * from (Select COL1, rownum rown from TAB where COL1 is not null ) a,
(Select COL2 , rownum rown from TAB where COL2 is not null) b,
(Select COL3 , rownum rown from TAB where COL3 is not null) c where a.rown=b.rown and a.rown=c.rown;

Edited by: JustasVred on Apr 13, 2009 8:46 AM

I tried this query. Its working, but it is eliminated the fourth row in the table.

COL1 COL2 COL3
1 b1 a2 y3
2 c1 c2 z3
3 a1 d2 x3

There is a fouth record for the column COL2.


-GV

Hi, It would be better if you posted CREATE TABLE and INSERT statements. It looks like you have 4 columns, but you only give 3 column names. I'll assume the table is called table_x, and the first column (the numbers 1-7, which is imoportant because it determines the output order) is called id.
It would help if you formatted the output and explained it. Type these 6 characters
{code}
(small letters only, inside curly brackets) before and after formatted text, to preserve spacing.
For example:

"The output I want is
{code}
R COL1 COL2 COL3
1 a1 d2 x3
2 b1 a2 y3
3 c1 c2 z3
4 NULL b2 NULL
{code}
The column r does not exist in my table; it has to be created by the query.
The values on the row with r=1 are the non-NULL values with the lowest id in table_x.
The values on the row with r=2 are the non-NULL values with the 2nd lowest id in table_x.
The values on the row with r=N are the non-NULL values with the N-th lowest id in table_x.
Every row of output muust have at least one non-NULL values in col1, col2 or col3."

If that's what you want, you can get it using the analytic ROW_NUMBER fucntion to generate the r column. This will be easiest if you un-pivot the data so that col1, col2 and col3 are all in the same column (on 3 different rows), then use a WEHERE clause to eliminate the NULLs, and finally pivot the results back into 4 columns, like this:

WITH	cntr	    AS
(
	SELECT	LEVEL	AS c
	FROM	dual
	CONNECT BY	LEVEL	<= 3
),	unpivoted   AS
(
	SELECT	id
	,	c
	,	CASE	c
			WHEN  1  THEN  col1
			WHEN  2  THEN  col2
			WHEN  3  THEN  col3
		END	AS txt
	FROM		table_x
	CROSS JOIN	cntr
)
,	got_r	AS
(
	SELECT	unpivoted.*
	,	ROW_NUMBER () OVER ( PARTITION BY c
			      	     ORDER BY  	  id
				   )  AS r
	FROM	unpivoted
	WHERE	txt	IS NOT NULL
)
SELECT	r
,	MAX (CASE WHEN c = 1 THEN txt END)	AS col1
,	MAX (CASE WHEN c = 2 THEN txt END)	AS col2
,	MAX (CASE WHEN c = 3 THEN txt END)	AS col3
FROM	got_r
GROUP BY	r
ORDER BY	r;

This assumes that col1, col2 and col3 all have the same data type. If not, explicitly convert them to the same type (probably VARCHAR2) in the CASE statement in the subquery I called unpivoted.

Hi,

COL1 COL2 COL3
1 a1 NULL NULL
2 b1 NULL x3
3 c1 NULL y3
4 NULL d2 z3
5 NULL a2 NULL
6 NULL c2 NULL
7 NULL b2 NULL

If i well understand

merge line 1 and line4
merge line2 and line5
merge line3 and line6
line 7

Ithink your desired output is :

a1 d2 z3
b1 a2 x3
c1 c2 y3
* b2 *

With this method you scan your table one time.

select  col1,col2,col3
from
(
select col1,col2,col3,found
 from t
model
return updated rows
dimension by ( row_number()over(partition by 1 order by rownum) rn )
measures(  count(1) over(partition by 1 ) cpt,rownum rr,col1,col2,col3, 0 found ,0 fixed, case when col1 is not null and col2 is null  and col3 is null then 1
             when col1 is  null and col2 is not null  and col3 is null then 2
             when col1 is  null and col2 is  null  and col3 is not null then 3
                                                                when col1 is not  null and col2 is  null  and col3 is not null then 13
              when col1 is not  null and col2 is not  null  and col3 is  null then 12
              when col1 is  null and col2 is not  null  and col3 is not  null then 23
end situation  ) ignore nav
 rules iterate(1000) until (iteration_number + 1 =cpt[1])
(fixed[1] =  case when situation[iteration_number+1]=1 then   min( case when  situation=23 and found=0  then  rr end )[any]
                            when situation[iteration_number+1]=2 then   min( case when  situation=13 and found=0 then  rr end )[any]
           when situation[iteration_number+1]=3 then   min( case when  situation=12 and found=0 then  rr end )[any]
           when situation[iteration_number+1]=13 then   min( case when  situation=2 and found=0 then  rr end )[any]
                             when situation[iteration_number+1]=12 then   min( case when  situation=3 and found=0 then  rr end )[any]
           when situation[iteration_number+1]=23 then   min( case when  situation=1  and found=0 then  rr end )[any] end,
col1[iteration_number+1] = case when situation[iteration_number+1]=2  then col1[fixed[1] ]
                                                when situation[iteration_number+1]=3 then col1[fixed[1] ]
               when situation[iteration_number+1]=23  then col1[fixed[1] ]
              else  col1[iteration_number+1]   end ,
col2[iteration_number+1] = case when situation[iteration_number+1]=1  then col2[fixed[1] ]
                                                when situation[iteration_number+1]=13  then col2[fixed[1] ]
               when situation[iteration_number+1]=3  then col2[fixed[1] ]
              else  col2[iteration_number+1]   end ,
col3[iteration_number+1] order by rn  = case when situation[iteration_number+1]=1 and fixed[1]  is present  then col3[fixed[1] ]
                                               when situation[iteration_number+1]=2 and fixed[1]  is present  then col3[fixed[1] ]
           when situation[iteration_number+1]=12 and fixed[1]  is present  then col3[fixed[1] ]
   else  col3[iteration_number+1] end,
found[fixed[1] ]=1,
situation[iteration_number+1] = case when fixed[1] is not null then null else situation[iteration_number+1]  end ))
where found=0
/

SQL> with t as (   
  2  select 'a1' COL1, NULL COL2, NULL COL3 from dual union all
  3  select  'b1' COL1, NULL COL2, 'x3' COL3 from dual union all
  4  select  'c1' ,NULL ,'y3' from dual union all
  5  select NULL, 'd2', 'z3' from dual union all
  6  select  NULL,'a2', NULL from dual union all
  7  select NULL ,'c2' ,NULL from dual union all
  8  select  NULL,'b2', NULL from dual )
  9  select  col1,col2,col3
 10  from 
 11  (
 12  select col1,col2,col3,found
 13   from t
 14  model 
 15  return updated rows
 16  dimension by ( row_number()over(partition by 1 order by rownum) rn )
 17  measures(  count(1) over(partition by 1 ) cpt,rownum rr,col1,col2,col3, 0 found ,0 fixed, case 
when col1 is not null and col2 is null  and col3 is null then 1 
 18               when col1 is  null and col2 is not null  and col3 is null then 2
 19               when col1 is  null and col2 is  null  and col3 is not null then 3
 20                                                                  when col1 is not  null and col2
 is  null  and col3 is not null then 13
 21                when col1 is not  null and col2 is not  null  and col3 is  null then 12 
 22                when col1 is  null and col2 is not  null  and col3 is not  null then 23
 23  end situation  ) ignore nav
 24   rules iterate(1000) until (iteration_number + 1 =cpt[1])
 25  (fixed[1] =  case when situation[iteration_number+1]=1 then   min( case when  situation=23 and 
found=0  then  rr end )[any]
 26                              when situation[iteration_number+1]=2 then   min( case when  situati
on=13 and found=0 then  rr end )[any]
 27             when situation[iteration_number+1]=3 then   min( case when  situation=12 and found=0
 then  rr end )[any] 
 28             when situation[iteration_number+1]=13 then   min( case when  situation=2 and found=0
 then  rr end )[any]  
 29                               when situation[iteration_number+1]=12 then   min( case when  situa
tion=3 and found=0 then  rr end )[any] 
 30             when situation[iteration_number+1]=23 then   min( case when  situation=1  and found=
0 then  rr end )[any] end,
 31  col1[iteration_number+1] = case when situation[iteration_number+1]=2  then col1[fixed[1] ]  
 32                                                  when situation[iteration_number+1]=3 then col1[
fixed[1] ]
 33                 when situation[iteration_number+1]=23  then col1[fixed[1] ] 
 34                else  col1[iteration_number+1]   end ,
 35  col2[iteration_number+1] = case when situation[iteration_number+1]=1  then col2[fixed[1] ]  
 36                                                  when situation[iteration_number+1]=13  then col
2[fixed[1] ]
 37                 when situation[iteration_number+1]=3  then col2[fixed[1] ] 
 38                else  col2[iteration_number+1]   end ,
 39  col3[iteration_number+1] order by rn  = case when situation[iteration_number+1]=1 and fixed[1] 
 is present  then col3[fixed[1] ]  
 40                                                 when situation[iteration_number+1]=2 and fixed[1
]  is present  then col3[fixed[1] ]
 41             when situation[iteration_number+1]=12 and fixed[1]  is present  then col3[fixed[1] ]

 42     else  col3[iteration_number+1] end,
 43  found[fixed[1] ]=1,
 44  situation[iteration_number+1] = case when fixed[1] is not null then null else situation[iterati
on_number+1]  end ))
 45  where found=0
 46  /

CO CO CO
-- -- --
a1 d2 z3
b1 a2 x3
c1 c2 y3
   b2

SQL> 

Regards salim.

Ok. Lets use outer join.

SQL> Select COL1, COL2, COL3 from (Select rownum rown from TAB where COL1 is not null
2 UNION
3 Select rownum rown from TAB where COL2 is not null
4 UNION
5 Select rownum rown from TAB where COL3 is not null) main,
6 (Select COL1, rownum rown from TAB where COL1 is not null ) a,
7 (Select COL2 , rownum rown from TAB where COL2 is not null) b,
8 (Select COL3 , rownum rown from TAB where COL3 is not null) c where main.rown=a.rown(+) and main.rown=b.rown(+) and main.rown=c.rown(+);

COL1 COL2 COL3
1 a1 d2 x3
2 b1 a2 y3
3 c1 c2 z3
4 NULL b2 NULL


P.S. Do not forget to give points for helpful answers:-)

Or

SQL>  with t as (   
  select 'a1' col1, null col2, null col3 from dual union all
  select 'b1' col1, null col2, 'x3' col3 from dual union all
  select 'c1',  null, 'y3' from dual union all
  select null, 'd2',  'z3' from dual union all
  select null, 'a2',  null from dual union all
  select null ,'c2',  null from dual union all
  select null, 'b2',  null from dual 
)
--
--
select col1, col2, col3 from 
(select rownum r1, col1 from t order by 1 nulls last) t1
 full outer join 
(select rownum r2, col2 from (select col2 from t order by 1 nulls last)) t2
 on t1.r1 = t2.r2
 full outer join
(select rownum r3, col3 from (select col3 from t order by 1 nulls last)) t3
 on t2.r2 = t3.r3
where coalesce(col1,col2,col3) is not null

CO CO CO
-- -- --
a1 a2 x3
b1 b2 y3
c1 c2 z3
   d2   

4 rows selected.

It is simple.

Select replace(cola, null, 'a',cola) from your table_name;

Edited by: sk123 on Apr 14, 2009 3:53 AM

I made 3 solutions :-)

create table modet as
select 'a1' COL1, NULL COL2, NULL COL3 from dual union all
select 'b1',NULL,'x3' from dual union all
select 'c1',NULL,'y3' from dual union all
select NULL,'d2','z3' from dual union all
select NULL,'a2',NULL from dual union all
select NULL,'c2',NULL from dual union all
select NULL,'b2',NULL from dual;

This solution used model caluse.
Hahaha this method scans table ones only B-)

select COL1,COL2,COL3
from (select COL1,COL2,COL3
        from modeT
       model
       dimension by(Row_Number() over(order by COL1,COL2,COL3) as rn,
                    nvl2(COL2,Row_Number() over(order by COL2),0) as rn2,
                    nvl2(COL3,Row_Number() over(order by COL3),0) as rn3)
       measures(COL1,COL2,COL3)
      rules(
      COL2[any,any,any] order by rn= max(COL2)[any,CV(rn),any],
      COL3[any,any,any] order by rn= max(COL3)[any,any,CV(rn)]))
 where coalesce(COL1,COL2,COL3) is not null
order by COL1,COL2,COL3;

COL1  COL2  COL3
----  ----  ----
a1    a2    x3
b1    b2    y3
c1    c2    z3
null  d2    null

This solution used OLAP :8}
We may use Lag and Lead instead of Olap which use range between

select COL1,Col2,Col3
from (select COL1,
      case when rn <= Col2Rn
           then max(Col2) over(order by Col2Rn
                               range between abs(Col2Rn-rn) preceding
                                         and abs(Col2Rn-rn) preceding)
           else max(Col2) over(order by Col2Rn
                               range between abs(Col2Rn-rn) following
                                         and abs(Col2Rn-rn) following) end as Col2,
      case when rn <= Col3Rn
           then max(Col3) over(order by Col3Rn
                               range between abs(Col3Rn-rn) preceding
                                         and abs(Col3Rn-rn) preceding)
           else max(Col3) over(order by Col3Rn
                               range between abs(Col3Rn-rn) following
                                         and abs(Col3Rn-rn) following) end as Col3
      from (select COL1,COL2,COL3,
            Row_Number() over(order by COL1,COL2,COL3) as rn,
            Row_Number() over(order by COL2,COL1,COL3) as Col2Rn,
            Row_Number() over(order by COL3,COL1,COL2) as Col3Rn
              from modet))
 where coalesce(COL1,Col2,Col3) is not null
order by COL1,Col2,Col3;

This solution used REFERENCE model ;-)
Today,I studied REFERENCE model

select *
  from modet model
  REFERENCE Col1model on
  (select Col1,Row_Number() over(order by Col1) as rn1 from modet where Col1 is not null)
    dimension by(rn1)
    measures(Col1)
  REFERENCE Col2model on
  (select Col2,Row_Number() over(order by Col2) as rn2 from modet where Col2 is not null)
    dimension by(rn2)
    measures(Col2)
  REFERENCE Col3model on
  (select Col3,Row_Number() over(order by Col3) as rn3 from modet where Col3 is not null)
    dimension by(rn3)
    measures(Col3)
  MAIN MAINmodel
  dimension by(Row_Number() over(order by COL1,Col2,Col3) as rn)
  measures(COL1,COL2,COL3)
  rules(
  Col1[any] = Col1model.Col1[cv(rn)],
  Col2[any] = Col2model.Col2[cv(rn)],
  Col3[any] = Col3model.Col3[cv(rn)])
order by rn;

**********************************************************
My homepage
http://www.geocities.jp/oraclesqlpuzzle/
My openSourceSoft OTN-thread viewer
880097