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.

On Dec 29, 12:24*pm, nova1...@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

On Dec 29, 4:54*am, nova1...@gmail.com wrote:
> On Dec 29, 12:24*pm, nova1...@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 --

On Dec 29, 4:24*am, nova1...@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.

Since there is no response to Mark's request for clarification, it
appears to me that the OP would like to include those rows where the
value in COL1 is the same as the values in COL2 and COL3, and allowing
nulls in the last column, the last two columns, or all three columns.
If we translate NULL values to an unlikely value, such as -999999,
using NVL, a simple WHERE clause should be sufficient. For example:
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
)
SELECT
COL1,
COL2,
COL3
FROM
T
WHERE
NVL(COL1,-999999)=NVL(COL2,NVL(COL1,-999999))
AND
NVL(COL2,NVL(COL1,-999999))=NVL(COL3,NVL(COL2,NVL(COL1,-999999)));

COL1 COL2 COL3
---------- ---------- ----------
1 1 1
2 2 2

3 3
4
5 5
6 6

7 rows selected.

Charles Hooper
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.