How to remove null values or how to bring multiple columns value into one

Question

0.00/5 (No votes)

See more:

SQL

Oracle

col1 col2 col3
100 null null
null 200 null
null null 300

output
col1
100
200
300

col col2 col3
100 200 300

Posted 13-Feb-15 0:04am

Member 11414389

Add a Solution

Comments

Jörgen Andersson 13-Feb-15 6:57am

Is this a table you want to fix or the result of a query?

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)

Afzaal Ahmad Zeeshan · Answer 1 · 2015-02-13T00:49:00

That is a totally wrong method or output that you're trying to perform and get. A table, is more like an object in real-world, sometime their data is null when they don't have anything specific to it. This output of yours, seems like arm of someone, leg of someone and heart of someone else. That is totally a bad call.

Anyhow, to remove the records where the values are null, you can use this following command,

SQL

DELETE FROM table_name WHERE column_name IS NULL

The above would remove those records, where the column_name is a null value. But, do not try to combine different records and make up one record, only because they were having their other fields left as null. That is not how database can be compact.

OriginalGriff · Answer 2 · 2015-02-13T00:58:00

I'm not sure you are trying to do the right thing: what happens if there are two rows with values in col1? I suspect you need to rethink your data structure a little.

But:
The first output version:

SQL

SELECT ISNULL(col1, 0)+ISNULL(col2, 0)+ISNULL(col3, 0) FROM MyTable

The second:

SQL

SELECT SUM(col1), SUM(col2), SUM(col3) FROM MyTable

_Asif_ · Answer 3 · 2015-02-13T01:53:00

Don't know what exactly you want but an alternative approach could be like

SQL

CREATE GLOBAL TEMPORARY TABLE TMP_TBLE
    (COL1 INT,
     COL2 INT,
     COL3 INT)
  ON COMMIT DELETE ROWS;

INSERT INTO TMP_TBLE(COL1, COL2, COL3)
SELECT 100, NULL, NULL
UNION ALL
SELECT NULL, 200, NULL
UNION ALL
SELECT NULL, NULL, 300

SELECT COALESCE(COL1, COL2, COL3)
FROm TMP_TBLE

SELECT  SUM(COL1) COL1, SUM(COL2) COL2, SUM(COL3) COL3
FROm TMP_TBLE

How to remove null values or how to bring multiple columns value into one

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0