SQL Server:根据记录的值返回列名 [英] 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     |

我希望能够构建一个查询来返回列名，其中列名的值(基于单个记录)= 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.

即我想要以下输出:

Field1
Field4

我一直在尝试对 sys.columns(和 sys.tables)进行各种连接，但到目前为止收效甚微.

I've been trying to do various joins against sys.columns (and sys.tables), but so far to little avail.

推荐答案

您也可以使用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 

这篇关于SQL Server:根据记录的值返回列名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！