Clever Geek Handbook

SQL Server: returns column names based on record value

I hope someone has a quick suggestion / solution for the following, based on the following example table:

|Field1 |Field2 |Field3 |Field4 |
|-------|-------|-------|-------|
| 1     | 0     | 0     | 1     |

I was hoping to build a query to return the column names where their value (based on one record) = 1. This is not relying on cursors or temporary tables.

those. I need the following output:

Field1
Field4

I am trying to do various joins with sys.columns

(and sys.tables

), but so far, little use.

+3


source to share


2 answers


You can also use Cross apply 

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
+5


source


You can do this using union all

for example:



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;
+2


source






More articles:


All Articles