# Re: Select some record

From: <nova1427_at_gmail.com>
Date: Sat, 29 Dec 2007 01:54:45 -0800 (PST)

On Dec 29, 12:24 pm, nova1..._at_gmail.com wrote:
> Dear All,
>
>
> 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
```
Received on Sat Dec 29 2007 - 03:54:45 CST

Original text of this message