Restrict fields in SELECT statement

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

SQL Server 2008 Forums

Transact-SQL (2008)

Restrict fields in SELECT statement

Author

Topic

evanburen
Posting Yak Master

167 Posts

Posted - 2013-09-19 : 12:36:20

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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-09-19 : 12:48:18

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?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-09-19 : 12:55:11

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?

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-19 : 13:49:38

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

djj

evanburen
Posting Yak Master

167 Posts

Posted - 2013-09-19 : 14:19:19

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'

evanburen
Posting Yak Master

167 Posts

Posted - 2013-09-19 : 14:21:02

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

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-20 : 10:00:23

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

evanburen
Posting Yak Master

167 Posts

Posted - 2013-09-20 : 14:43:04

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.

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-09-20 : 14:51:26

Good luck, if I gave you a direction then I am happy.

djj

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources