Find all columns of a single row that are True

[TerryGef]

Hi, Need help in an SQL problem:
I have a table that has individual records, There are 29 fields.
One is a unique ID, 14 are True/False and 14 are text.
Using a query to find one record, I'd like the SQL to Display only the fields that are True.

So, in an example:
SELECT *
FROM table
WHERE ID = 1
(show all fields that are True)
There might be 4 out of 14 that are True and the rest would be false.
I want the SQL to display those True fields in a list.

Thanks in advance.
Terry

 

[denimined]

Given:
Table TBL with fields ID and boolean fields T1-T5
Data:
[tt]1 - T/F/F/F/T
2 - T/T/T/T/T
3 - F/F/F/F/F
[/tt]
I'm not sure that you can get there, since what you are saying is that for row 1, you want to see fields T1 and T5 - the only ones TRUE. But then for row 2, you want to see fields T1-T5 as they are all true. Finally, row 3 does not appear as T1-T5 are all false.

Obviously, there is no way to represent this as a grid display of data. Since each row can return a different number of attributes.
However, you can present a result attribute [that is useless to further queries, but useful for humans] that will show you the result.

select ID, 
       case when T1 then 'T1 ' else '' end 
     + case when T2 then 'T2 ' else '' end 
     + case when T3 then 'T3 ' else '' end 
     + case when T4 then 'T4 ' else '' end 
     + case when T5 then 'T5 ' else '' end as result
from TBL
where T1 or T2 or T3 or T4 or T5

From the above data example, this would return:
[tt]1 T1 T5
2 T1 T2 T3 T4 T5
[/tt]

 

[TerryGef]

Your post is right on to what I am looking for, but when I plug in the statement, I am getting a missing operator error. Here is my query:
SELECT ID,
case when FB_chk Then ' FB_chk=True' else '' end
+ case when TW_chk Then ' TW_chk=True' else '' end
+ case when PI_chk Then ' PI_chk=True' else '' end
+ case when FR_chk Then ' FR_chk=True' else '' end
+ case when RS_chk Then ' RS_chk=True' else '' end
+ case when FS_chk Then ' FS_chk=True' else '' end
+ case when YT_chk Then ' YT_chk=True' else '' end
+ case when DS_chk Then ' DS_chk=True' else '' end
+ case when MU_chk Then ' MU_chk=True' else '' end
+ case when LN_chk Then ' LN_chk=True' else '' end
+ case when TB_chk Then ' TB_chk=True' else '' end
+ case when FF_chk Then ' FF_chk=True' else '' end
+ case when SU_chk Then ' SU_chk-True' else '' end
+ case when PC_chk Then ' PC_chk=True' else '' end
+ case when IG_chk Then ' IG_chk=True' else '' end
+ case when RD_chk Then ' RD_chk=True' else '' end
+ case when EM_chk Then ' EM_chk=True' else '' end as Result
FROM tbl_links
WHERE ID = 1 AND FB_chk OR TW_chk OR PI_chk OR FR_chk OR RS_chk OR FS_chk OR YT_chk OR DS_chk OR MU_chk OR LN_chk OR TB_chk OR FF_chk OR SU_chk OR PC_chk OR IG_chk OR RD_chk OR EM_chk

Please, what am I missing?

 

[gmmastros]

The problem is that SQL Server wants to evaluate an expression, not a column. As such, you should modify your query to:

SELECT ID,
case when FB_chk = 1 Then ' FB_chk=True' else '' end
+ case when TW_chk = 1 Then ' TW_chk=True' else '' end
+ case when PI_chk = 1 Then ' PI_chk=True' else '' end
+ case when FR_chk = 1 Then ' FR_chk=True' else '' end
+ case when RS_chk = 1 Then ' RS_chk=True' else '' end
+ case when FS_chk = 1 Then ' FS_chk=True' else '' end
+ case when YT_chk = 1 Then ' YT_chk=True' else '' end
+ case when DS_chk = 1 Then ' DS_chk=True' else '' end
+ case when MU_chk = 1 Then ' MU_chk=True' else '' end
+ case when LN_chk = 1 Then ' LN_chk=True' else '' end
+ case when TB_chk = 1 Then ' TB_chk=True' else '' end
+ case when FF_chk = 1 Then ' FF_chk=True' else '' end
+ case when SU_chk = 1 Then ' SU_chk-True' else '' end
+ case when PC_chk = 1 Then ' PC_chk=True' else '' end
+ case when IG_chk = 1 Then ' IG_chk=True' else '' end
+ case when RD_chk = 1 Then ' RD_chk=True' else '' end
+ case when EM_chk = 1 Then ' EM_chk=True' else '' end as Result	
FROM tbl_links
WHERE ID = 1 
      AND (FB_chk = 1 OR TW_chk = 1 OR PI_chk = 1 OR FR_chk = 1 
           OR RS_chk = 1 OR FS_chk = 1 OR YT_chk = 1 OR DS_chk = 1 
           OR MU_chk = 1 OR LN_chk = 1 OR TB_chk = 1 OR FF_chk = 1 
           OR SU_chk = 1 OR PC_chk = 1 OR IG_chk = 1 OR RD_chk = 1 
           OR EM_chk = 1)

Note that I put "= 1" in the case expression and in the where clause. Also notice that I added parenthesis around the "or'd" columns. Without the parenthesis, you would have gotten all rows where any of the or'd columns are 1.

Also note that since you are using True/False columns (actually BIT data type), you should be able to use the logical OR operator in the where clause, like this:

SELECT ID,
case when FB_chk = 1 Then ' FB_chk=True' else '' end
+ case when TW_chk = 1 Then ' TW_chk=True' else '' end
+ case when PI_chk = 1 Then ' PI_chk=True' else '' end
+ case when FR_chk = 1 Then ' FR_chk=True' else '' end
+ case when RS_chk = 1 Then ' RS_chk=True' else '' end
+ case when FS_chk = 1 Then ' FS_chk=True' else '' end
+ case when YT_chk = 1 Then ' YT_chk=True' else '' end
+ case when DS_chk = 1 Then ' DS_chk=True' else '' end
+ case when MU_chk = 1 Then ' MU_chk=True' else '' end
+ case when LN_chk = 1 Then ' LN_chk=True' else '' end
+ case when TB_chk = 1 Then ' TB_chk=True' else '' end
+ case when FF_chk = 1 Then ' FF_chk=True' else '' end
+ case when SU_chk = 1 Then ' SU_chk-True' else '' end
+ case when PC_chk = 1 Then ' PC_chk=True' else '' end
+ case when IG_chk = 1 Then ' IG_chk=True' else '' end
+ case when RD_chk = 1 Then ' RD_chk=True' else '' end
+ case when EM_chk = 1 Then ' EM_chk=True' else '' end as Result	
FROM tbl_links
WHERE ID = 1 
      AND (FB_chk | TW_chk | PI_chk | FR_chk | RS_chk | FS_chk | 
           YT_chk | DS_chk | MU_chk | LN_chk | TB_chk | FF_chk | 
           SU_chk | PC_chk | IG_chk | RD_chk | EM_chk = 1)

Notice that you still need to put "= 1" on the or'd columns.


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

 

[TerryGef]

okie-dokie...
So, I tried both above examples and am getting a "Missing Operator" error.
At this point, I am wondering if it makes a difference that this is SQL being used on the Internet compared to Access vb or whatever. Normally my SQL can be used anywhere, but I'm stuck.

Thanks in advance for your assistance.
Terry

 

[gmmastros]

SQL being used on the Internet"

I don't know what this means? It's important that we know what the database engine is. Some SQL will work on most database engines, but each database engine has it's own set of functionality for certain things.

In this case, it could be the CASE/WHEN functionality that is causing the problem.

Can you run this and post the results back here:

Select @@Version

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

 

[TerryGef]

Hi, George...

The host server is running: "Windows Server 8, version 6".

I had wondered if the case statements are the problem.

This code I'm trying to run is on a website, I'm not sure if that is the issue either.
The actual database that I am accessing is an Access 2000 .mdb

But my understanding is the SQL is primarily working with the server and what it supports?
Not sure, just speculating.

Thanks for your help.
Terry

 

[xlbo]

The Case statement is the issue. MS Access != SQL Server

2 options for Access are to use nested iif() statements or to write a VB function and call that

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question