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
Bjørn H. Sandvik
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
P ரதீப்
source
to share
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
Gordon linoff
source
to share