select all field except field wich have NULL value

Question

Post reply

select all field except field wich have NULL value

sinamora

Grasshopper

Points: 24
More actions
March 9, 2010 at 9:39 pm

#139281

hi,
i have a table like below :
Day1 Day2 Day3 Day4 Day5
12 3 NULL 43 11
i want to select all field except field wich have NULL
i want query return table like below,
Day1 Day2 Day4 Day5
12 3 43 11
can somebody help me???
thanks before :]

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Kingston Dhasian SSCoach Points: 19799 More actions · Answer 1

What would be the expected result if you have data similar to this???

DECLARE@tblTable TABLE

(

Day1 INT,

Day2 INT,

Day3 INT,

Day4 INT,

Day5 INT

)

INSERT INTO @tblTable

SELECT 12, 3, NULL, 43, 11

UNION ALL

SELECT 12, NULL, 3, 43, 11

UNION ALL

SELECT 12, 3, 43, NULL, 11

SELECT * FROM @tblTable

Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Paul White SSC Guru Points: 150468 More actions · Answer 2

Whatever the requirements turn out to be, you can remove NULLs and get the data into an easy format with UNPIVOT:

DECLARE @T

TABLE (

Day1 INTEGER NULL,

Day2 INTEGER NULL,

Day3 INTEGER NULL,

Day4 INTEGER NULL,

Day5 INTEGER NULL

)

INSERT @T

(Day1, Day2, Day3, Day4, Day5)

SELECT 12, 3, NULL, 43, 11 UNION ALL

SELECT 12, NULL, 3, 43, 11 UNION ALL

SELECT 12, 3, 43, NULL, 11;

SELECT U.Name,

U.Value

FROM @T T1

UNPIVOT (

Value FOR

Name IN (Day1, Day2, Day3, Day4, Day5)

) U

ORDER BY

U.Name,

U.Value;

Output:

Day1 12

Day2 3

Day3 3

Day3 43

Day4 43

Day5 11

Paul

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 3

below will give you those column names and values which have not any null value

;with cte as

(

SELECT ColumnNames, Value, COUNT(ColumnNames) AS cnt

FROM

(

SELECT day1, day2, day3, day4, day5 FROM days

) p

UNPIVOT

( Value For ColumnNames IN (day1, day2, day3, day4, day5) ) AS U

GROUP BY ColumnNames, Value

)

, cte1 as

(

SELECT Max(cnt) mxcnt FROM cte

)

SELECT ColumnNames, Value FROM cte, cte1

WHERE cte.cnt = cte1.mxcnt

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

sinamora (3/9/2010)
Day1 Day2 Day3 Day4 Day5
12 3 NULL 43 11

what would happen/expected result, if there will be more than one row?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

vaibhav.tiwari SSCommitted Points: 1976 More actions · Answer 5

Bhuvnesh (3/10/2010)
sinamora (3/9/2010)
Day1 Day2 Day3 Day4 Day5
12 3 NULL 43 11
what would happen/expected result, if there will be more than one row?

That is what Mr. Kingston had asked earlier.

Vaibhav K Tiwari
To walk fast walk alone
To walk far walk together

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 6

vaibhav.tiwari (3/10/2010)
Bhuvnesh (3/10/2010)
sinamora (3/9/2010)
Day1 Day2 Day3 Day4 Day5
12 3 NULL 43 11
what would happen/expected result, if there will be more than one row?
That is what Mr. Kingston had asked earlier.

Yep... and it still hasn't been answered.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sinamora Grasshopper Points: 24 More actions · Answer 7

sinamora

Grasshopper

Points: 24

March 14, 2010 at 10:28 pm

#1133509

thanks for all. it's very help me 🙂

Paul White SSC Guru Points: 150468 More actions · Answer 8

sinamora (3/14/2010)
thanks for all. it's very help me 🙂

Looks like Jeff isn't getting his answer.

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 9

Paul White (3/14/2010)
sinamora (3/14/2010)
thanks for all. it's very help me 🙂
Looks like Jeff isn't getting his answer.

Heh... Diodes, check valves, and one way streets are useful tools... just not on a forum. 😀 It happens.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Paul White SSC Guru Points: 150468 More actions · Answer 10

University flashback: Zener diode

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi

vijay.s SSCrazy Points: 2161 More actions · Answer 11

vijay.s

SSCrazy

Points: 2161

March 16, 2010 at 1:40 am

#1134278

Unpivot is the best way to achieve this task.

Paul White SSC Guru Points: 150468 More actions · Answer 12

vijay.s (3/16/2010)
Unpivot is the best way to achieve this task.

It has certainly been suggested at least twice already, but I am intrigued to know how you can say it is 'best' without knowing what the requirements are?

Paul White
All articles available on SQL.kiwi
@SQL_Kiwi