Null elimination - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Null elimination

Author

Topic

koushikchandra
Starting Member

24 Posts

Posted - 2011-01-26 : 05:40:39

Hi,

I have data set like below :

col1 col2 col3
1 cust1 NULL
1 cust2 NULL
1 NULL prod1
1 NULL prod2

I want to get an output like :

col1 col2 col3
1 cust1 prod1
1 cust2 prod2

How can I get that easily?

When I am writing a query like below :

select col1,max(col2), max(col3)
from(
(select 1 col1,'cust1' col2,NULL col3
union all
select 1 col1,'cust2' col2,null col3)
union all
(select 1 col1,NULL col2,'prod1' col3
union all
select 1 col1,null col2,'prod2' col3)
) qry
group by col1

It is giving me an output like below :

col1 col2 col3
1 cust2 prod2

But I want to get the output as
col1 col2 col3
1 cust1 prod1
1 cust2 prod2

Please help.

Regards,
Koushik

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-26 : 05:49:21

How will you decide which col2 is matching to which col3?
To make it more difficult and more real:
What will you do when there are more than 2 different values for col2 and col3?

Maybe you should come up with "near reality example data" and the used table structure.

No, you're never too old to Yak'n'Roll if you're too young to die.

koushikchandra
Starting Member

24 Posts

Posted - 2011-01-26 : 05:59:43

Tried to give the example with more correct data.

The data set with me is:
col1 col2 col3
2 cust1 NULL
3 cust2 NULL
1 cust NULL
1 cust NULL
1 NULL prod
1 NULL prod
2 NULL prod1
3 NULL prod2

and I want the output as :
col1 col2 col3
1 cust prod
1 cust prod
2 cust1 prod1
3 cust2 prod2

Regards,
Koushik

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-26 : 06:28:20

So again my question:
How will you decide which col2 is matching to which col3?

No, you're never too old to Yak'n'Roll if you're too young to die.

koushikchandra
Starting Member

24 Posts

Posted - 2011-01-26 : 09:27:13

you can assume the following are two different data sets:

SET 1:
col1 col2 col3
2 cust1 NULL
3 cust2 NULL
1 cust NULL
1 cust NULL

SET 2:
col1 col2 col3
1 NULL prod
1 NULL prod
2 NULL prod1
3 NULL prod2

The joining will be on "col1" only. Now where there is duplicate rows in col1 there col2 for SET 1 and col3 for SET 2 is also duplicate and I want that duplicate rows as output. menas two rows for col1=1

col1 col2 col3
1 cust prod
1 cust prod
2 cust1 prod1
3 cust2 prod2

Hope this clarifies.

Regards,
Koushik

koushikchandra
Starting Member

24 Posts

Posted - 2011-01-26 : 10:08:52

I find a way of to resolve this. Please let me know if you have any other easier way to do it.

select col1, col2, col3
into set_1
from
(
select 2 col1,'cust1' col2,NULL col3
union all
select 3 col1,'cust2' col2,null col3
union all
select 1 col1,'cust' col2,NULL col3
union all
select 1 col1,'cust' col2,null col3
) qry

select col1, col2, col3
into set_2
from
(
select 1 col1,NULL col2,'prod' col3
union all
select 1 col1,null col2,'prod' col3
union all
select 2 col1,NULL col2,'prod1' col3
union all
select 3 col1,null col2,'prod2' col3
)qry

WITH set_1_plus_r_num AS
(
SELECT col1, col2
, ROW_NUMBER () OVER ( PARTITION BY col1
ORDER BY col2
) AS r_num
FROM set_1
)
, set_2_plus_r_num AS
(
SELECT col1, col3
, ROW_NUMBER () OVER ( PARTITION BY col1
ORDER BY col3
) AS r_num
FROM set_2
)
SELECT s1.col1
, s1.col2
, s2.col3
FROM set_1_plus_r_num s1
JOIN set_2_plus_r_num s2 ON s1.col1 = s2.col1
AND s1.r_num = s2.r_num

Regards,
Koushik

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources