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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

evanburen
Posting Yak Master

167 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

3873 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
352 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

167 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

167 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
352 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

167 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
352 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  
 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.14 seconds. Powered By: Snitz Forums 2000