CODEWITHSUNDEEP
sqlsql-server
weeraa
weeraa

Reputation: 1195

SQL avoid null fields

This is my table

SQL Table

I need to write a query for getting a dataset like below.(Avoid null fields and group by id) enter image description here

Please give me direction....

Upvotes: 1

Views: 92

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522762

Try grouping by the id:

SELECT id,
       MAX(col1) AS col1,
       MAX(col2) AS col2,
       MAX(col3) AS col3,
       MAX(col4) AS col4
FROM test
GROUP BY id

The reason this works is that in SQL Server MAX ignores NULL values, so only the single VARCHAR value (e.g. 'ABC') will be retained during the grouping operation.

The data in your test table appear to be the intermediate step of a pivot query.

Upvotes: 5

Related Questions