display column name when bit on is found for any fields

Question

Post reply

display column name when bit on is found for any fields

DataAroma

SSC Enthusiast

Points: 184
More actions
October 18, 2011 at 12:03 pm

#245494

say that we have a table naming "abc" with their values
id name address email
1 0 1 0
2 0 1 0
3 0 0 0
4 1 0 0
pls note that name, address, email are all bits.
i want to write a T-sql statement which displays only the column names if the column name has atleast a ON bit i.e 1.
so, the result will display name, address.
could you please help with the statement..

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

what if more than one bit is true?

SELECT id,'NAME' As Results from YourTable where name=1

UNION

SELECT id,'ADDRESS' As Results from YourTable where address=1

UNION

SELECT id, 'EMAIL' As Results from YourTable where email=1

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Sean Lange SSC Guru Points: 286590 More actions · Answer 2

nicetohaveyou (10/18/2011)
say that we have a table naming "abc" with their values
id name address email
1 0 1 0
2 0 1 0
3 0 0 0
4 1 0 0
pls note that name, address, email are all bits.
i want to write a T-sql statement which displays only the column names if the column name has atleast a ON bit i.e 1.
so, the result will display name, address.
could you please help with the statement..

Is this what you are looking for?

select id, case name when 1 then 'name' else null end as NameCol, [your other columns here]

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

DataAroma SSC Enthusiast Points: 184 More actions · Answer 3

i want a more dynamic one where we dont need to supply the column names...it should work for any number of columns and we should hard code the name of the columns in the select statement.

Could you please help me with this.

Thanks...

John Michael Robertson SSC Eights! Points: 988 More actions · Answer 4

I assume you mean to not hard code the column names for the dynamic method. If you did intend to hard-code columns, then you can manually fill in the @tableCols variable in the provided code sample.

Here is one option to try that mimics the same table structure listing, as you requested, the column name if set to "ON" or blank if "OFF".

I also have another option where you can generate a comma separated list for each of the column names set to "ON". The statement is commented out with a header comment specifying what it outputs.

I hope this sample helps answer your request.

declare @tableSchema varchar(255) = 'dbo'

declare @tableNm varchar(255) = 'abc';

declare @tableCols table (colNm varchar(255));

insert into @tableCols (colNm)

select sc.[name]

from sys.schemas s

join sys.objects so

on so.schema_id=s.schema_id

and so.[name] = @tableNm

join sys.columns sc

on sc.object_id=so.object_id

join sys.types st

on st.system_type_id=sc.system_type_id

and st.[name]='bit' -- check for BIT field

where s.name=@tableSchema

;

declare @bitColSelect nvarchar(max) = N'';

------ compile the bit columns together to output a comma separated list of Column Names having a True/ON bit value

----select @bitColSelect = @bitColSelect + COALESCE(N'CASE WHEN ' + colNm + N'=1 THEN ''' + colNm + N','' ELSE '''' END +', N'') from @tableCols;

-- compile the bit columns together to output the same columns populated with the Column Name having a True/ON bit value

select @bitColSelect = @bitColSelect + COALESCE(N'[' + colNm + N']=CASE WHEN [' + colNm + N']=1 THEN ''' + colNm + N''' ELSE '''' END ,', N'') from @tableCols;

-- remove the ending join character

select @bitColSelect = LEFT(@bitColSelect,LEN(@bitColSelect)-1);

-- create the dynamic SELECT statement

--, this assumes the "ID" field is known otherwise you will have to query for the column, possibly for the Primary Key column

declare @sqlString nvarchar(max)=N'';

select @sqlString = N'select [id], ' + @bitColSelect + N' from [' + @tableSchema + N'].[' + @tableNm + N'] ';

-- output generated sql statement

exec (@sqlString)