CODEWITHSUNDEEP
sqlsql-server
Rohit Raj
Rohit Raj

Reputation: 133

Find values which are present in all columns in a Cable

I would like a SQL Server query which finds the Values in a cell which fills multiple columns. For example, if I have table

 ID     Value1    Value2     Value3
 1        2        NULL      NULL 
 1       NULL      3         NULL 
 1       NULL      NULL       4
 1       3.4       NULL      NULL 
 2       NULL      3         NULL
 2       NULL      NULL      NULL
 3       NULL      NULL       91

As in the table above, only 2 of the columns can be filled at a time(First is ID and 2nd is either of Value1, 2 or 3) and ID can be repeated multiple times.

I want to return the ID as only 1 because 1 is the only ID that populates all the three other columns. 2 fills only Value2 and all the other values of 2nd iteration of 2 are NULL where as 3 is present only in Column Value3. Is there someway that I can find the Id's which fill all the other columns.

I would love to do this preferably without a cursor but I can go for cursor if it's compulsory. Thanks

EDIT

Desired Table:

ID 1

The Statement should return only the filtered IDs which populate all the other columns.

Upvotes: 1

Views: 66

Answers (2)

Ram Mehta
Ram Mehta

Reputation: 519

Something for you try if you want some less lines of code: 

select ID from dbo.Table_1  group by ID having count(Value1) > 0 AND count(Value2) > 0 AND count(Value3) >  0

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Try this

SELECT id, 
FROM TableName
GROUP BY id
HAVING MAX(value1) IS NOT NULL AND 
       MAX(value2) IS NOT NULL AND
       MAX(value3) IS NOT NULL

Upvotes: 4

Related Questions