Ignore column if result set is null

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Ignore column if result set is null

Author

Topic

Finboo
Starting Member

5 Posts

Posted - 2013-07-12 : 07:26:30

Hi all. New to the forum so please bear with me!
I'm not sure there even is an answer for this but here goes..

Is there a way to ignore a column/variable when the whole result set for the applied filter (where) is null?
Not all clients have data for every variable, i.e. some variables are client specific. There are too many variables and clients to amend the select query every time so I just want to ignore a col if its null.
I hope that makes sense (my inability to describe it might explain my inability to find anything related to it!)
The next step would be to run all clients' data in one go using, I believe, a cursor, but one step at a time!
Many thanks in advance for any help and pointers

Finboo
Starting Member

5 Posts

Posted - 2013-07-12 : 07:43:58

Just realised that I missed out some of my post!

Is there a way to ignore a column/variable when the whole result set for the applied filter (where) is null?
I have a list of variables that I want to select for a number of clients (running each client seperately to produce an Excel 'extract' of the data).Not all clients have data for every variable, i.e. some variables are client specific. There are too many variables and clients to amend the select query every time so I just want to ignore a col if its null.
I hope that makes sense (my inability to describe it might explain my inability to find anything related to it!)
The next step would be to run all clients' data in one go using, I believe, a cursor, but one step at a time!
Many thanks in advance for any help and pointers

MIK_2008
Master Smack Fu Yak Hacker

1054 Posts

Posted - 2013-07-12 : 08:02:27

Sounds to me something like --> WHERE ColumnName1 = ISNULL(@variable,ColumnName1)

However, it would be better to illustrate your issue with the help of an example. That way it would be easier for you and for us too. Provide sample data in consumable format and desired ouput for sample data.

Cheers
MIK

Finboo
Starting Member

5 Posts

Posted - 2013-07-12 : 08:19:38

Thanks MIK

The query would be..
select
Client,
Var1,
Var2,
Var3,
Var4,
Var5,
Var6,
Var7,
Var8
from Table1
where client = 'ClientA'
GO
select
Client,
Var1,
Var2,
Var3,
Var4,
Var5,
Var6,
Var7,
Var8
from Table1
where client = 'ClientB'
GO And so on
However, ClientA will have no data in VAR2 or VAR3
so instead of...
Client Var1 Var2 Var3 Var4 Var5 Var6 Var7 Var8
ClientA Result NULL NULL Result Result Result Result Result
ClientA Result NULL NULL Result Result Result Result Result

I would want it to return..
Client Var1 Var4 Var5 Var6 Var7 Var8
ClientA Result Result Result Result Result Result
ClientA Result Result Result Result Result Result

And ClientB has no data in VAR6 or VAR8 and would return..

Client Var1 Var2 Var3 Var4 Var5 Var7
ClientA Result Result Result Result Result Result
ClientA Result Result Result Result Result Result
Does that make anymore sense?!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-12 : 08:49:29

unpivot and then pivot back


;with unpvt as
(
	select	Client, VarResult, ColNo = row_number() over (partition by Client order by VarCol)
	from	Table1 t
		unpivot
		(
			 VarResult for VarCol in
	     		 (Var1, Var2, Var3, Var4, Var5, ...)
		) p
)
select	Client, 
	[1] as Var1,
	[2] as Var2,
	[3] as Var3,
	[4] as Var4,
	[5] as Var5,
        ...
from	unpvt d
	pivot
	(
		max(VarResult)
		for	ColNo in ([1], [2], [3], [4], [5], ...)
	) p

KH
[spoiler]Time is always against us[/spoiler]

Finboo
Starting Member

5 Posts

Posted - 2013-07-12 : 09:32:44

Wow, KH, that is way beyond anything I even knew you could do in SQL!
I am trying a subset and getting errors. May I post my code to maybe see where I am going wrong?
Many thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-07-12 : 21:45:45

sure. Post your query here

KH
[spoiler]Time is always against us[/spoiler]

Finboo
Starting Member

5 Posts

Posted - 2013-07-16 : 10:18:58

Here is my 'attempt'!

;with unpvt as
(
select Inst, VarResult, ColNo = row_number() over (partition by uni order by VarCol)
from [>>Basic] b
INNER JOIN [>>Basic_comments] on b.id = [>>Basic_comments].id
INNER JOIN [>>DEC_Help] on b.id=[>>DEC_Help].id
INNER JOIN [>>Funding] on b.id=[>>Funding].id
INNER JOIN [>>apply_where] on b.id=[>>apply_where].id
INNER JOIN [>>study_where] on b.id=[>>study_where].id
INNER JOIN [>2013SH] s on b.id=s.id
unpivot
(
VarResult for VarCol in
(uni,
ISBSB,
pl_studentid,
SSS_EXT2,
uni_country, SSS_EXT1
)
) p
)
select [0] as uni,
[1] as ISBSB,
[2] as pl_studentid,
[3] as SSS_EXT2,
[4] as uni_country,
[5] as SSS_EXT1

from unpvt d
pivot
(
count(VarResult)
for ColNo in ([0], [1], [2], [3], [4], [5])
) p

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources