• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 87
  • Last Modified:

Getting column names based on value

Given a set number columns with values True or False, what is the most efficient way of getting the columns with value TRUE. e.g. select column_name from table where column_name.value='TRUE'

I have the query below which works, but I am trying to see if there is a more efficient/condensed/less lines of code way to do it?

Declare @col varchar(255), @cmd varchar(max)
DECLARE getinfo cursor for
SELECT c.name FROM sys.tables t JOIN sys.columns c ON t.Object_ID = c.Object_ID and c.name IN ('Maths','English','History','General Science')
WHERE t.Name = 'classes'

OPEN getinfo

FETCH NEXT FROM getinfo into @col

WHILE @@FETCH_STATUS = 0

BEGIN
    SELECT @cmd = 'IF EXISTS (SELECT * FROM classes WHERE ([' + @col + '] =''True'' ) and id=3) BEGIN print ''' + @col + ''' end'
	EXEC(@cmd)

    FETCH NEXT FROM getinfo into @col
END

CLOSE getinfo
DEALLOCATE getinfo

Open in new window

0
takwirirar
Asked:
takwirirar
1 Solution
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Maybe this solution:
SELECT CASE Maths 
                   WHEN ''True'' THEN 'Maths'
                   ELSE NULL
                END, 
              CASE English
                   WHEN ''True'' THEN 'English'
                   ELSE NULL
                END, 
             CASE History
                   WHEN ''True'' THEN 'History'
                   ELSE NULL
                END, 
           CASE [General Science]
                   WHEN ''True'' THEN 'General Science'
                   ELSE NULL
                END
FROM classes 
WHERE id=3

Open in new window

0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now