Exclude columns with Null

Question

I am trying to create a view in SQL Server 2012 that excludes columns where the entry is null (represent by 0 here so it's easier to read). My base data is this

╔════╦══════╦══════╦══════╦══════╦══════╗
║ ID ║ Col1 ║ Col2 ║ Col3 ║ Col4 ║ Col5 ║
╠════╬══════╬══════╬══════╬══════╬══════╣
║  1 ║    1 ║    0 ║    0 ║    0 ║    5 ║
║  2 ║    1 ║    2 ║    3 ║    0 ║    5 ║
║  3 ║    0 ║    0 ║    0 ║    0 ║    0 ║
║  4 ║    0 ║    2 ║    3 ║    0 ║    0 ║
╚════╩══════╩══════╩══════╩══════╩══════╝

What I would like to return would be

╔════╦══════╦══════╦══════╦══════╗
║ ID ║ Res1 ║ Res2 ║ Res3 ║ Res4 ║
╠════╬══════╬══════╬══════╬══════╣
║  1 ║    1 ║    5 ║    0 ║    0 ║
║  2 ║    1 ║    2 ║    3 ║    5 ║
║  3 ║    0 ║    0 ║    0 ║    0 ║
║  4 ║    2 ║    3 ║    0 ║    0 ║
╚════╩══════╩══════╩══════╩══════╝

In this case, since column 4 had value 0 in all entries it was not included as a result. ID1 returned 1 and 5 skipping the 0s, and had 0s to fill since ID2 had 4 columns. If all values were 0, this would either just return Res1 with 0 or no columns and just the IDs.

Hope this is clear. I'm having trouble explaining it.

Why is there a 6 in row ID 4 col Res3?

Will
– Will

2015-11-16 19:57:34 +00:00
Commented Nov 16, 2015 at 19:57 — Will
– Will, Commented Nov 16, 2015 at 19:57
Because typing hurts.

Andrew O'Brien
– Andrew O'Brien

2015-11-16 20:02:17 +00:00
Commented Nov 16, 2015 at 20:02 — Andrew O'Brien
– Andrew O'Brien, Commented Nov 16, 2015 at 20:02

Nimantha · Accepted Answer · 2022-01-06 03:16:13Z

2

You cannot really do what you want to do. Queries, views, and user-defined functions returns a specific set of columns. The set of columns is defined in advance. So, you cannot remove them.

You could create a dynamic query that only included columns that are currently not NULL. Or, you could create an XML data structure with the columns you want. But, the columns in a view are fixed when the view is created, and cannot be added and removed when the view is run.

edited Jan 6, 2022 at 3:16

Nimantha

6,5376 gold badges32 silver badges78 bronze badges

answered Nov 16, 2015 at 19:53

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Evaldas Buinauskas · Accepted Answer · 2015-11-16 20:02:25Z

0

As Gordon Linoff told, you could potentially do this using Dynamic SQL, however - I wouldn't.

Handle with this logic in your application code, not database. SQL Server is not supposed to do these taks well. Code's going to be hard to maintain.

answered Nov 16, 2015 at 20:02

Evaldas Buinauskas

14.2k11 gold badges63 silver badges119 bronze badges

Comments

Guffa · Accepted Answer · 2015-11-16 20:10:06Z

0

Well, it's possible, but the query gets some really complicated expressions.

The first column is quite easy:

Res1 = coalesce(Col1, Col2, Col3, Col4, Col5, 0),

The second column gets more complicated, as it has to determine where the first used value were to get the second value:

Res2 = case
    when Col1 is not null then coalesce(Col2, Col3, Col4, Col5, 0)
    when Col2 is not null then coalesce(Col3, Col4, Col5, 0)
    when Col3 is not null then coalesce(Col4, Col5, 0)
    when Col4 is not null then coalesce(Col5, 0)
    else 0
  end,

From there it gets even more complicated. The next value has to determine where the first two used values were. I'm not even going to try to write that.

answered Nov 16, 2015 at 20:10

Guffa

703k111 gold badges760 silver badges1k bronze badges

Comments

Rabbit · Accepted Answer · 2015-11-16 21:30:07Z

0

If you drop the requirement where the result excludes columns where it is all null, you can do this with an UNPIVOT and then a PIVOT.

answered Nov 16, 2015 at 21:30

Rabbit

5073 silver badges9 bronze badges

1 Comment

Andrew O'Brien Over a year ago

Yea, I looked into that but I end up having 60ish rows and only 2 have something that needs to be seen.

Collectives™ on Stack Overflow

Exclude columns with Null

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related