How to get NULL value in column?

Question

1.00/5 (1 vote)

See more:

Check all column and get `value` + `null` record.

**Original table** 

  
    Id	Tname	MobileNumber	OtherMobileNumber
    1	john	 9737943110	      9865986532
    2	marry	 null    	      8765986521
    3	dally	 5487986512	      null

(-) is like a value,you assume any kind

**Expected Result**

    Id	Tname	Conatct
    1	john	9737943110			
    2	john	9865986532		
    3	marry	null
    4   marry   8765986521
    5   dally   5487986512	      
    6	dally	null

What I have tried:

Am trying but can't get expected result

Posted 9-Jun-19 21:31pm

Promis Patel

Updated 18-Jun-19 19:55pm

v3

Add a Solution

Comments

OriginalGriff 10-Jun-19 3:37am

This is not a good question - we cannot work out from that little what you are trying to do.
Remember that we can't see your screen, access your HDD, or read your mind - we only get exactly what you type to work with.
So instead of "-" which means nothing, use sample data instead:
1, Mike, 012345, 06789
2, Joe, 09876, 054321
3, Jane, 063452, null
Result:
1, ???, ???
2, ???, ???
3, ???, ???

And explain why you want a null in the third row, or exactly what you do want!
Use the "Improve question" widget to edit your question and provide better information.

Promis Patel 10-Jun-19 3:44am

Ok,I update the question.

OriginalGriff 10-Jun-19 3:48am

:sigh:
And what are the results you expect from the data you gave us?
Guessing isn't a good way to give you a solution...
Oh, and show us what you have tried - that may help as well.

Promis Patel 10-Jun-19 5:08am

Please check am update the question

CHill60 10-Jun-19 5:13am

Show us the code you have tried so that we can guide you better

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2019-06-09T23:16:00

Solution 1

The easiest way to do this is to use UNION[^] but based on other homework questions I've seen recently your tutor may want you to use UNPIVOT[^]

Posted 9-Jun-19 23:16pm

CHill60

Comments

Maciej Los 19-Jun-19 1:56am

Caroline, why?
What about COALESCE() function (already mentioned in my answer)?
[EDIT]
Sorry, my bad. I haven't read the question carefully.
5ed!

Santosh kumar Pithani · Answer 2 · 2019-06-18T18:54:00

SQL

CREATE TABLE #TEMP (
	Id INT
	,Tname VARCHAR(100)
	,MobileNumber NUMERIC(10)
	,OtherMobileNumber NUMERIC(10)
	)

INSERT INTO #TEMP

VALUES (1,'john',9737943110,9865986532)
	  ,(2,'marry',NULL,8765986521)
	  ,(3,'dally',5487986512,NULL)

SELECT 
     ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS ID
	,Tname
	,Contact
FROM #TEMP
CROSS APPLY (
	        VALUES (MobileNumber)
		          ,(OtherMobileNumber)
	        ) AS Tab(Contact)

Maciej Los · Answer 3 · 2019-06-18T19:55:00

Solution 3

If you want to get only first non-null value, the simplest way is to use COALESCE()[^] function:

SQL

SELECT Tname, COALESCE(MobileNumber, OtherMobileNumber) AS Contact
FROM YourTable

BTW: I'd avoid to display NULLs as a contact number.

Good luck!

Posted 18-Jun-19 19:55pm

Maciej Los

Updated 18-Jun-19 20:02pm

v3