Re: Select some record

On Dec 29, 4:54 am, nova1..._at_gmail.com wrote:
> On Dec 29, 12:24 pm, nova1..._at_gmail.com wrote:
>
>
>
>
>
> > Dear All,
>
> > I have problem, Please help.
>
> > There is table called t:
>
> > with t as (
> >         select 1 Col1, 1 Col2, 1 Col3 from dual union all
> >         select 1, 1, 2 from dual union all
> >         select 1, 2, 1 from dual union all
> >         select 1, 2, 2 from dual union all
> >         select 2, 1, 1 from dual union all
> >         select 2, 1, 2 from dual union all
> >         select 2, 2, 1 from dual union all
> >         select 2, 2, 2 from dual union all
> >         select 2, 1, 1 from dual union all
> >         select 2, 1, 2 from dual union all
> >         select 2, 2, 1 from dual union all
> >         select 2, 2, 2 from dual union all
> >         select NULL, NULL, NULL from dual union all
> >         select 3, 3, NULL from dual union all
> >         select 4, NULL, NULL from dual union all
> >         select 5, 5, NULL from dual union all
> >         select 5, 6, NULL from dual union all
> >         select 6, 5, NULL from dual union all
> >         select 6, 6, NULL from dual
> > )
>
> > Table (  t  )
> > Col1    Col2    Col3
> > 1       1       1
> > 1       1       2
> > 1       2       1
> > 1       2       2
> > 2       1       1
> > 2       1       2
> > 2       2       1
> > 2       2       2
> > 2       1       1
> > 2       1       2
> > 2       2       1
> > 2       2       2
>
> > 3       3
> > 4
> > 5       5
> > 5       6
> > 6       5
> > 6       6
>
> > Question: how can do like this result?
>
> > The result should be like this:
>
> > Table (  result  )
> > Col1    Col2    Col3
> > 1       1       1
> > 2       2       2
>
> > 3       3
> > 4
> > 5       5
> > 6       6
>
> > Note: Make sure the empty cells are included.
>
> Sorry, there is mistake in table t
>
> with t as (
>         select 1 Col1, 1 Col2, 1 Col3 from dual union all
>         select 1, 1, 2 from dual union all
>         select 1, 2, 1 from dual union all
>         select 1, 2, 2 from dual union all
>         select 2, 1, 1 from dual union all
>         select 2, 1, 2 from dual union all
>         select 2, 2, 1 from dual union all
>         select 2, 2, 2 from dual union all
>         select NULL, NULL, NULL from dual union all
>         select 3, 3, NULL from dual union all
>         select 4, NULL, NULL from dual union all
>         select 5, 5, NULL from dual union all
>         select 5, 6, NULL from dual union all
>         select 6, 5, NULL from dual union all
>         select 6, 6, NULL from dual
> )
>
> Table (  t  )
> COL1    COL2    COL3
> 1       1       1
> 1       1       2
> 1       2       1
> 1       2       2
> 2       1       1
> 2       1       2
> 2       2       1
> 2       2       2
>
> 3       3
> 4
> 5       5
> 5       6
> 6       5
> 6       6- Hide quoted text -
>
> - Show quoted text -

I for one am not particularly good at recognizing requirements from just a listing of the desired result set. It might lead someone to post if you listed the requirements in words to go along with the example.

• Mark D Powell --