SQL Server : return column names based on a record's value
I’m hoping someone has a quick suggestion/solution to the following, based on the following sample table:
|Field1 |Field2 |Field3 |Field4 | |-------|-------|-------|-------| | 1 | 0 | 0 | 1 |
I was hoping to be able to build a query to return the column names where their value (based on a single record) = 1. This, without leaning on cursors or temp tables.
I.e. I would like the following output:
I’ve been trying to do various joins against
sys.tables), but so far to little avail.
2 Solutions collect form web for “SQL Server : return column names based on a record's value”
You can also use
SELECT Cname FROM Tablename CROSS apply (VALUES('Field1',Field1), ('Field2',Field2), ('Field3',Field3), ('Field4',Field4)) ca (cname, data) WHERE data = 1
To work dynamically use this.
CREATE TABLE test ( Field1 INT, Field2 INT, Field3 INT, Field4 INT ) INSERT INTO test VALUES ( 1,0,0,1 ) DECLARE @collist VARCHAR(max)='', @sql NVARCHAR(max) SELECT @collist += '(''' + COLUMN_NAME + ''',' + COLUMN_NAME + '),' FROM INFORMATION_SCHEMA.columns WHERE TABLE_NAME = 'test' AND COLUMN_NAME LIKE 'Field%' AND TABLE_SCHEMA = 'dbo' SELECT @collist = LEFT(@collist, Len(@collist) - 1) SET @sql =' SELECT Cname FROM test CROSS apply (VALUES' + @collist + ') ca (cname, data) WHERE data = 1 ' EXEC Sp_executesql @sql
You can do this using
union all, for instance:
select 'Field1' from table t where Field1 = 1 union all select 'Field2' from table t where Field2 = 1 union all select 'Field3' from table t where Field3 = 1 union all select 'Field4' from table t where Field4 = 1;