some columns have NULLS - 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

General SQL Server Forums

New to SQL Server Programming

some columns have NULLS

Author

Topic

limericklad1974
Starting Member

39 Posts

Posted - 2012-05-08 : 10:23:37

Hi there

I have a database table with 6 columns
ID
Test1
Test2
Test3
test4
Test5

The data in my table looks like this

1 NULL NULL 3.01 4.98 NULL
2 NULL 4.39 3.97 NULL 8.75
3 NULL 5.54 NULL NULL 6.54
4 2.45 NULL NULL NULL 8.74

What I need to do is create little tables, where I group the data from column 2 and 5 for example...

The result needs to be something like this (for grouping column 2 and 6)

2 NULL 8.75
3 NULL 6.54
4 2.45 8.74

So, I would need to ignore row 1 because columns 2 and 6 have a NULL value but I would want to display rows 2 and 3 even though column 2 has a NULL, column 6 has data

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:04:03

You need multiple inserts for this


INSERT INTO Table1
Select Test1,Test5
FROM table
WHERE Test1 IS NOT NULL
OR Test5 IS NOT NULL
...

Can i ask reason for splitting the data into smaller tables like this? Are you aware that by doing this you're increasing data redundancy? How are you planning to keep these child tables consistent with master tables reg. DML operations taking place on master?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2012-05-08 : 11:45:46

quote:
Originally posted by visakh16

You need multiple inserts for this

INSERT INTO Table1
Select Test1,Test5
FROM table
WHERE Test1 IS NOT NULL
OR Test5 IS NOT NULL
...

Can i ask reason for splitting the data into smaller tables like this? Are you aware that by doing this you're increasing data redundancy? How are you planning to keep these child tables consistent with master tables reg. DML operations taking place on master?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sorry I didnt mean to give the impression I wanted to split the data into further tables. What I want to do is create some stored precedures to view the data, but I dont want to view the entire dataset, I just want to display certain columns

for instance, column 2 and 6 might be related so I want to display a table of results with just these two columns displayed.

Are you sure your code will work for this?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 11:55:10

if you just want retrieval remove that insert part


Select Test1,Test5
FROM table
WHERE Test1 IS NOT NULL
OR Test5 IS NOT NULL
...

Based on columns you want you can change column names in select as well as in where above

Are you sure your code will work for this?
why not try it and see for yourself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

limericklad1974
Starting Member

39 Posts

Posted - 2012-05-08 : 12:40:19

quote:
Originally posted by visakh16

if you just want retrieval remove that insert part

Select Test1,Test5
FROM table
WHERE Test1 IS NOT NULL
OR Test5 IS NOT NULL
...

Based on columns you want you can change column names in select as well as in where above

Are you sure your code will work for this?
why not try it and see for yourself?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I think I oversimplified the problem... it seems like there is another stored procedure running in the background of the page I am working on....

I will try to get a better explanation sorted out and get the database tables, data and procedures up here for someone to look at

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-08 : 12:44:01

Ok..that sounds fine

Unless you give full background we wont be able to get full picture as we dont have access to your system!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources