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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Restrict fields in SELECT statement
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

155 Posts

Posted - 09/19/2013 :  12:36:20  Show Profile  Reply with Quote
SELECT [ReviewID]
,[V_Name]
,[V_Clear]
,[V_Tone]
,[V_Prompt]
,[V_Name]
,[V_SSN]
,[V_Adrs]
,[V_Phon1]
,[V_Phon2]
,[V_Email]
,[V_DOB]
FROM Calls

Each of these fields named "V_" is a bit field and I only want the fields in the results where the value = 1. In other words, I don't want to do this all of the fields appear in the results.

SELECT [ReviewID]
,[V_Name]
,[V_Clear]
,[V_Tone]
,[V_Prompt]
,[V_Name]
,[V_SSN]
,[V_Adrs]
,[V_Phon1]
,[V_Phon2]
,[V_Email]
,[V_DOB]
FROM Calls
WHERE V_Name = 1 OR V_Clear = 1 OR V_Tone = 1 etc.

If only V_Name and V_Clear = 1 and the others all equal 0, then the results should only contain ReviewID, V_Name and V_Clear.

Thanks

James K
Flowing Fount of Yak Knowledge

3712 Posts

Posted - 09/19/2013 :  12:48:18  Show Profile  Reply with Quote
YOu can make the ones that are not = 1 into nulls.
....
CASE WHEN [V_Name] = 1 THEN [V_Name] ELSE NULL END AS [V_Name],
... etc


Or are you thinking of a jagged result set - i.e., where the first row might have two columns, the second might have 8 columns and so on. That is not something that T-SQL is fond of. If you indeed wanted such a result set, what would you name those columns?
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 09/19/2013 :  12:55:11  Show Profile  Reply with Quote
The only way I know of with SQL Server is to use dynamic SQL, which seems like a wonky solution. Do you mind saying why you are trying to limit the columns returned?
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 09/19/2013 :  13:49:38  Show Profile  Reply with Quote
Can the output be two columns? ReviewID and a string of names?

djj
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 09/19/2013 :  14:19:19  Show Profile  Reply with Quote
quote:
Originally posted by James K

YOu can make the ones that are not = 1 into nulls.
....
CASE WHEN [V_Name] = 1 THEN [V_Name] ELSE NULL END AS [V_Name],
... etc


Or are you thinking of a jagged result set - i.e., where the first row might have two columns, the second might have 8 columns and so on. That is not something that T-SQL is fond of. If you indeed wanted such a result set, what would you name those columns?



The CASE statement isn't quite what I was looking for because the NULL columns are still in the result set. Having all of the fields in one column that = 1 would work. I would name it 'Passed Calls'
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 09/19/2013 :  14:21:02  Show Profile  Reply with Quote
quote:
Originally posted by djj55

Can the output be two columns? ReviewID and a string of names?

djj



Yes, that would work as well. Thanks
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 09/20/2013 :  10:00:23  Show Profile  Reply with Quote
What I was thinking is you could build the column name list.
When I asked the question I had something in mind but when I try to come up with sample code I am not sure.
Here is what I came up with today:
select ReviewID, 
    CASE WHEN [V_Name]   = 1 THEN '[V_Name]' ELSE '' END + 
    CASE WHEN [V_Clear]  = 1 THEN '[V_Clear]' ELSE '' END + 
    CASE WHEN [V_Tone]   = 1 THEN '[V_Tone]' ELSE '' END + 
    CASE WHEN [V_Prompt] = 1 THEN '[V_Prompt]' ELSE '' END + 
    CASE WHEN [V_Name]   = 1 THEN '[V_Name]' ELSE '' END + 
    CASE WHEN [V_SSN]    = 1 THEN '[V_SSN]' ELSE '' END + 
    CASE WHEN [V_Adrs]   = 1 THEN '[V_Adrs]' ELSE '' END + 
    CASE WHEN [V_Phon1]  = 1 THEN '[V_Phon1]' ELSE '' END + 
    CASE WHEN [V_Phon2]  = 1 THEN '[V_Phon2]' ELSE '' END + 
    CASE WHEN [V_Email]  = 1 THEN '[V_Email]' ELSE '' END + 
    CASE WHEN [V_DOB]    = 1 THEN '[V_DOB]' ELSE '' END AS Hits
from Calls


djj
Go to Top of Page

evanburen
Posting Yak Master

155 Posts

Posted - 09/20/2013 :  14:43:04  Show Profile  Reply with Quote
quote:
Originally posted by djj55

What I was thinking is you could build the column name list.
When I asked the question I had something in mind but when I try to come up with sample code I am not sure.
Here is what I came up with today:
select ReviewID, 
    CASE WHEN [V_Name]   = 1 THEN '[V_Name]' ELSE '' END + 
    CASE WHEN [V_Clear]  = 1 THEN '[V_Clear]' ELSE '' END + 
    CASE WHEN [V_Tone]   = 1 THEN '[V_Tone]' ELSE '' END + 
    CASE WHEN [V_Prompt] = 1 THEN '[V_Prompt]' ELSE '' END + 
    CASE WHEN [V_Name]   = 1 THEN '[V_Name]' ELSE '' END + 
    CASE WHEN [V_SSN]    = 1 THEN '[V_SSN]' ELSE '' END + 
    CASE WHEN [V_Adrs]   = 1 THEN '[V_Adrs]' ELSE '' END + 
    CASE WHEN [V_Phon1]  = 1 THEN '[V_Phon1]' ELSE '' END + 
    CASE WHEN [V_Phon2]  = 1 THEN '[V_Phon2]' ELSE '' END + 
    CASE WHEN [V_Email]  = 1 THEN '[V_Email]' ELSE '' END + 
    CASE WHEN [V_DOB]    = 1 THEN '[V_DOB]' ELSE '' END AS Hits
from Calls


djj



Yes, I think this will work for me. Thanks very much.
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 09/20/2013 :  14:51:26  Show Profile  Reply with Quote
Good luck, if I gave you a direction then I am happy.

djj
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.11 seconds. Powered By: Snitz Forums 2000