SQL - return all field names with a specific value for a given record 1

[stelljohann]

SQL 2005. I would like to do a select query that returns all fields for a specific record that are equal to a specific value.

Table1 has a number of fields (Field1, Field2, Field3...). Each of these fields can either be True or False. I would like to query a specific record and produce a list of which fields have a value of 'True'.

Is this possible, and if so can anyone provide an example? TIA.

Thanks,
stelljohann
--The number of times an uninteresting thing happens is an interesting thing.--

 

[gmmastros]

try this:

Select YourColumn(s)
From   YourTable
[!]Where  'True' in (Field1, Field2, Field3, Field4)[/!]

There are other ways to handle this type of query. You could use dynamic sql or even a union query. Some methods may have advantages over others.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Re-reading your question, I may have misunderstood. If this is true, let me know.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stelljohann]

Thanks for the Quick response George. Your query is returning the actual values for all specified fields though. Using the table below, I'd like to query Record1 and get a result of 'Field1, Field2'. Likewise, if I query Record2, the result should be 'Field2, Field4'.

Table1
--------
Recordid | Field1 | Field2 | Field3 | Field4
----------------------------------------------
Record1 | True | True | False | False
Record2 | False | True | False | True

Thanks.

Thanks,
stelljohann
--The number of times an uninteresting thing happens is an interesting thing.--

 

[gmmastros]

I had a feeling that I misunderstood your question, and I do apologize for that.

I suggest you take a look at this query, which I believe woudl provide the results you are looking for.

Declare @Temp 
Table   (Id VarChar(10), 
        Field1 VarChar(5), 
        Field2 VarChar(5), 
        Field3 VarChar(5), 
        Field4 VarChar(5))

Insert Into @Temp Values('Record1','True','True','False','False')
Insert Into @Temp Values('Record2','False','True','False','True')
        
Select *,
       Case When Field1 = 'True' Then 'Field1,' Else ' End
       + Case When Field2 = 'True' Then 'Field2,' Else ' End
       + Case When Field3 = 'True' Then 'Field3,' Else ' End
       + Case When Field4 = 'True' Then 'Field4,' Else ' End As FieldsWithTrue
From   @Temp
Where  Id = 'Record1'

Note that there will be a trailing comma on the data. I did not add the required code to remove it because it would make the query harder to understand.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[stelljohann]

Perfect. This is exactly what I needed. I had to make one minor syntax change. I changed the single quote after the Else statements to two single quotes. Thanks for your help.

Thanks,
stelljohann
--The number of times an uninteresting thing happens is an interesting thing.--

 

[gmmastros]

Weird. I had 2 single quotes in the code I posted. It mysteriously changed to one single-quote.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

2 single-quotes outside a code block: here '.

2 single-quotes inside a code block here '.

2 single-quotes inside a quote block here'.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

2 single-quotes are shown in the preview page, but not when submitted. I guess this is a half-assed (poorly implemented) attempt at preventing SQL injection on this site.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom