How to avoid the null values

Question

How to avoid the null values...

Table1

ID Value1 Value2

001 Rajan  null
001 Vijayan null
001 null ravi
001 null sudeep
002 kumar null
002 null venkat
.....

I don't want to display null values.

Expected Output

 ID Value1 Value2

    001 Rajan  ravi
    001 Vijayan sudeep
    002 kumar venkat
    .....

How to make a query for the above condition

Need Query Help

The expected output doesn't make much sense. Please explain the logic governing it. What happens when you have a row with both `Value1` and `Value2` populated (or `null`)? — Oded, Dec 25 '11 at 09:43
I just answered a very similar question here: http://stackoverflow.com/questions/8628573/select-query-by-type-wise/8628953#8628953 — TetonSig, Dec 25 '11 at 09:55
How do you know that `Rajan` and `ravi` belong together (and not `Rajan` and 'sudeep`) ?? After all - all of them have `ID = 001` ..... This output is not properly and deterministically defined..... — marc_s, Dec 25 '11 at 10:10
Perhaps adding the query to your post might help in seeing what could be done about fixing it to achieve your goal. — Andriy M, Dec 25 '11 at 11:55

marc_s · Accepted Answer · 2011-12-25T10:26:31.077

Unless you explain in more detail how those values from Value1 and Value2 columns belong together, and only if that "matching" is really deterministic, then you could do something like this:

DECLARE @temp TABLE (ID INT, Value1 VARCHAR(20), Value2 VARCHAR(20))

INSERT INTO @temp
        (ID, Value1, Value2)
VALUES
        (1, 'Rajan', NULL),
        (3, 'Vijayan', NULL),
        (1, NULL, 'Ravi'),
        (3, NULL, 'sudeep'),
        (2, 'kumar', NULL),
        (2, NULL, 'venkat')

SELECT DISTINCT
   ID, 
   (SELECT Value1 FROM @temp t2 WHERE t2.ID = t.ID AND Value1 IS NOT NULL) AS 'Value1',
   (SELECT Value2 FROM @temp t2 WHERE t2.ID = t.ID AND Value2 IS NOT NULL) AS 'Value2'
FROM
   @temp t

That would give you one row for each value of ID, with the non-NULL value for Value1 and the non-null value for Value2.

But as your question stands right now, this approach doesn't work, since you have multiple entries for the same ID - and no explanation as to how to match the two separate values together....

So as it stands right now, I would say there is no deterministic and proper solution for your question. You need to provide more information so we can find a solution for you.

Update: if you would update to SQL Server 2005 or newer, you could do something like two nested CTE's - but in that case, too, you would have to define some rule / ordering as to how the two variants with ID = 001 are joined together.....

Something like:

DECLARE @temp TABLE (ID INT, Value1 VARCHAR(20), Value2 VARCHAR(20))

INSERT INTO @temp
        (ID, Value1, Value2)
VALUES
        (1, 'Rajan', NULL),
        (1, 'Vijayan', NULL),
        (1, NULL, 'Ravi'),
        (1, NULL, 'sudeep'),
        (2, 'kumar', NULL),
        (2, NULL, 'venkat')

;WITH Value1CTE AS
(
    SELECT ID, Value1,
       ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value1) AS 'RowNum'
    FROM @temp
    WHERE Value1 IS NOT NULL
),
Value2CTE AS
(
    SELECT ID, Value2,
       ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Value2) AS 'RowNum'
    FROM @temp
    WHERE Value2 IS NOT NULL
)
SELECT 
   v1.ID, 
    v1.Value1, v2.Value2
FROM
   Value1CTE v1
INNER JOIN 
    Value2CTE v2 ON v1.ID = v2.ID AND v1.RowNum = v2.RowNum

would give you a reproducible output of:

ID  Value1  Value2
1   Rajan   Ravi
1   Vijayan sudeep
2   kumar   venkat

This is under the assumption that given two entries with the SAME ID, you want to sort (ORDER BY) the actual values (e.g. Rajan before Vijayan and Ravi before sudeep --> there you'd join Rajan and Ravi together, as well as Vijayan and sudeep).

But again: this is in SQL Server 2005 and newer only - no equivalent in SQL Server 2000, unforutnately.....

score 0 · Answer 2 · answered Dec 25 '11 at 16:43

0

SELECT i1.ID, i2.Value1, i3.Value2
FROM Table1 i1, Table1 i2, Table1 i3
WHERE i1.ID = i2.ID and i1.ID = i3.ID and i2.Value1 IS NOT NULL and i3.Value2 IS NOT NULL;

answered Dec 25 '11 at 16:43

Amir Nasr

241
1
2
3

How to avoid the null values

2 Answers2