Avoid NULL Values - 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)

Avoid NULL Values

Author

Topic

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2009-03-10 : 14:57:32

How to aviod null values from a table?

Ex:
table1
col1 col2 col3 col4
123 pen null null
null null 123 west st. 75683

i need out put as

table2
col1 col2 col3 col4
123 pen 123 west st. 75683

Can any one help me out in this asap?

-Thanks N Regards,
Chinna.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 15:00:37

[code]Select Max(col1) as col1,Max(col2)as col2........
from yourtable[/code]

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2009-03-10 : 15:08:30

If i have 1000 rows then it will display only one row that is having maximum value right? how can i get 1000 rows?

-Thanks N Regards,
Chinna.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-10 : 15:11:40

Post your full query.I assume you are looking for cross-tab results.

kkiranvr
Yak Posting Veteran

54 Posts

Posted - 2009-03-10 : 15:31:06

what exactly i want is,
1. i have 1000 rows and 4 columns,
2. while i am inserting records from the flat file
col1 is filling out with 250 values, and showing col2, col3 and
col4 null.
col2 is filling out with 250 values, and showing col1, col3 and
col4 null.
Same this is happening for both col3 and col4.

So table count here is 1000

3. Now i am looking for the records
Col1 --- 250 values.
Col2 --- 250 values.
Col3 --- 250 values and
Col4 --- 250 values.
and table count should be 250 not 1000.

For inserting records i was using SSIS package.

-Thanks N Regards,
Chinna.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 14:07:53

then that shows its the problem with your source data. also while merging how will you decide which value of col1 shoule be merged with which value of col2,col3,... or do you not care about that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-11 : 14:11:32

if you dont care about the order of values to be merged, youcan use this


SELECT t1.Col1,t2.Col2,t3.Col3,t4.Col4
FROM
(SELECT ROW_NUMBER() OVER (ORDER BY Col1) AS Seq,Col1
 FROM Table
 WHERE Col2 IS NULL
AND Col3 IS NULL
AND Col4 IS NULL)t1
JOIN (SELECT ROW_NUMBER() OVER (ORDER BY Col2) AS Seq,Col2
 FROM Table
 WHERE Col1 IS NULL
AND Col3 IS NULL
AND Col4 IS NULL
)t2
ON t2.Seq=t1.Seq
JOIN
(
SELECT ROW_NUMBER() OVER (ORDER BY Col3) AS Seq,Col3
 FROM Table
 WHERE Col2 IS NULL
AND Col1 IS NULL
AND Col4 IS NULL
)t3
ON t3.Seq=t1.Seq
JOIN (
SELECT ROW_NUMBER() OVER (ORDER BY Col4) AS Seq,Col4
 FROM Table
 WHERE Col2 IS NULL
AND Col3 IS NULL
AND Col1 IS NULL
)t4
ON t4.Seq=t1.Seq

rohitkumar
Constraint Violating Yak Guru

472 Posts

Posted - 2009-03-11 : 17:15:16

do you have 4 diff flat files each with one column data?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources