SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Ignore column if result set is null
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Finboo
Starting Member

United Kingdom
5 Posts

Posted - 07/12/2013 :  07:26:30  Show Profile  Reply with Quote
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

United Kingdom
5 Posts

Posted - 07/12/2013 :  07:43:58  Show Profile  Reply with Quote
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
Go to Top of Page

MIK_2008
Flowing Fount of Yak Knowledge

Pakistan
1052 Posts

Posted - 07/12/2013 :  08:02:27  Show Profile  Reply with Quote
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
Go to Top of Page

Finboo
Starting Member

United Kingdom
5 Posts

Posted - 07/12/2013 :  08:19:38  Show Profile  Reply with Quote
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?!
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 07/12/2013 :  08:49:29  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

Finboo
Starting Member

United Kingdom
5 Posts

Posted - 07/12/2013 :  09:32:44  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17598 Posts

Posted - 07/12/2013 :  21:45:45  Show Profile  Reply with Quote
sure. Post your query here


KH
Time is always against us

Go to Top of Page

Finboo
Starting Member

United Kingdom
5 Posts

Posted - 07/16/2013 :  10:18:58  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000