how to select only those without null

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

General SQL Server Forums

New to SQL Server Programming

how to select only those without null

Author

Topic

faebsi
Starting Member

10 Posts

Posted - 2009-01-29 : 16:38:52

hi guys

here ive got another question:

table:

name lastn year month day
john NULL NULL NULL NULL
NULL taylor NULL NULL NULL
NULL NULL 1978 NULL NULL
NULL NULL NULL 12 NULL
NULL NULL NULL NULL 25
lukas NULL NULL NULL NULL
NULL cartney NULL NULL NULL
NULL NULL 1980 NULL NULL
NULL NULL NULL 11 NULL
NULL NULL NULL NULL 31

sql-code ?

excected result:

name lastn year month day
john taylor 1978 12 25
lukas cartney 1980 11 31

how could i do that?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-01-29 : 19:55:56

[code]Select Coalesce(col1,col2,col3,col4,col5) from table[/code]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2009-01-29 : 20:06:04

If the table has only the five columns you have listed, and the output should return the 2 rows as you have indicated, you cannot guarantee that it will be returned exactly as you have shown.

This is because the database has no way of knowing that John is associated with Taylor and not with Cartney. So your query might return John Cartney and Lukas Taylor instead of the other way around.

If one were to pick the data from successive columns from each row it would appear that it will work the way you have indicated; but a table is an unordered set, so the ordering is not guaranteed, and so the outcome is unpredictable.

faebsi
Starting Member

10 Posts

Posted - 2009-01-30 : 01:29:04

quote:
Originally posted by sodeep

Select Coalesce(col1,col2,col3,col4,col5) from table

Unfortunately this shows me only one column with all data (without the NULL).

Result using coalesce:
john
taylor
1978
12
25
lukas
cartney
1980
11
31

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 01:39:06

faebsi, can you post your original query? it seems like you're trying for cross tab.

faebsi
Starting Member

10 Posts

Posted - 2009-01-30 : 01:44:51

SELECT COALESCE(name, lastn, year, month, day)
FROM testtable1

RESULT:
john
taylor
1978
12
25
lukas
cartney
1980
11
31

When I use just:
SELECT * FROM testtable1 I've got:

RESULT:
john NULL NULL NULL NULL
NULL taylor NULL NULL NULL
NULL NULL 1978 NULL NULL
NULL NULL NULL 12 NULL
NULL NULL NULL NULL 25
lukas NULL NULL NULL NULL
NULL cartney NULL NULL NULL
NULL NULL 1980 NULL NULL
NULL NULL NULL 11 NULL
NULL NULL NULL NULL 31

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-30 : 01:53:36

isnt there a field which identifies what records should be merged?

faebsi
Starting Member

10 Posts

Posted - 2009-01-30 : 01:54:54

no.. actually the solution would be to hide all NULLs and then the result would be correct.

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-30 : 05:55:42

[code]
Hi Try this

declare @table table (rowno int identity(1,1), name varchar(33) ,lname varchar(33),year int,month int , day int)
insert into @table(name, lname, year ,month, day)
select 'john', NULL, NULL, NULL, NULL union all
select NULL, 'taylor', NULL, NULL, NULL union all
select NULL, NULL, 1978 ,NULL ,NULL union all
select NULL ,NULL, NULL, 12, NULL union all
select NULL, NULL, NULL, NULL, 25 union all
select 'lukas', NULL, NULL, NULL, NULL union all
select NULL, 'cartney', NULL, NULL ,NULL union all
select NULL, NULL, 1980, NULL, NULL union all
select NULL, NULL, NULL ,11, NULL union all
select NULL, NULL, NULL, NULL, 31 union all
select 'kountinya',NULL,NULL,NULL,NULL union all
select NULL, 'prasad', NULL, NULL ,NULL union all
select NULL, NULL, 1986, NULL, NULL union all
select NULL, NULL, NULL ,9, NULL union all
select NULL, NULL, NULL, NULL, 9

select max(c.name),max(c.lname),max(c.year),max(c.month),max(c.day)
from
(
select a.rowno,b.name,b.lname,b.year,b.month,b.day from
(select rowno from @table where day is not null )a
cross apply
(select * from @table where rowno > coalesce((select max(rowno) from @table where rowno < a.rowno and day is not null),0) and rowno<=a.rowno) b
) c
group by c.rowno

Jai Krishna
[/code]

Jai Krishna
Constraint Violating Yak Guru

333 Posts

Posted - 2009-01-30 : 05:59:28

[code]
Or

declare @table table ( name varchar(33) ,lname varchar(33),year int,month int , day int)
insert into @table(name, lname, year ,month, day)
select 'john', NULL, NULL, NULL, NULL union all
select NULL, 'taylor', NULL, NULL, NULL union all
select NULL, NULL, 1978 ,NULL ,NULL union all
select NULL ,NULL, NULL, 12, NULL union all
select NULL, NULL, NULL, NULL, 25 union all
select 'lukas', NULL, NULL, NULL, NULL union all
select NULL, 'cartney', NULL, NULL ,NULL union all
select NULL, NULL, 1980, NULL, NULL union all
select NULL, NULL, NULL ,11, NULL union all
select NULL, NULL, NULL, NULL, 31 union all
select 'kountinya',NULL,NULL,NULL,NULL union all
select NULL, 'prasad', NULL, NULL ,NULL union all
select NULL, NULL, 1986, NULL, NULL union all
select NULL, NULL, NULL ,9, NULL union all
select NULL, NULL, NULL, NULL, 9

select identity(int,1,1) as rowno,* into temptab from @table

select max(c.name),max(c.lname),max(c.year),max(c.month),max(c.day)
from
(
select a.rowno,b.name,b.lname,b.year,b.month,b.day from
(select rowno from temptab where day is not null )a
cross apply
(select * from temptab where rowno > coalesce((select max(rowno) from temptab where rowno < a.rowno and day is not null),0) and rowno<=a.rowno) b
) c
group by c.rowno

[/code]

Jai Krishna

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 06:12:37

What column is defining the "order" of records?

E 12°55'05.63"
N 56°04'39.26"

tosscrosby
Aged Yak Warrior

676 Posts

Posted - 2009-01-30 : 09:09:32

It appears his database doesn't have keys and is not normalized in any fashion:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=118860

Terry

-- Procrastinate now!

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-30 : 09:25:02

faebsi, what query do you use to populate the table in first place?

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources