CODEWITHSUNDEEP
sqlsql-server
MDP
MDP

Reputation: 53

SQL Server : get column name of a table using condition

I have a sample table here - I want to get all the columns that have the value of 1 only. Is it possible?

enter image description here

Upvotes: 2

Views: 1122

Answers (1)

Shushil Bohara
Shushil Bohara

Reputation: 5656

Its absolutely possible but the process is lengthy, I am using loop to check each column's data exists by retrieving column name from sys.columns. Please try this if it helps you in any term:

Here I am checking each column for value 1 only

CREATE TABLE testing(val1 INT, val2 INT, val3 INT)
INSERT INTO testing VALUES
(1, 0, 1),(1, 0, 1),(1, 1, 1)

Table: testing
val1    val2    val3
1       0       1
1       0       1
1       1       1

DECLARE @sql NVARCHAR(500), @list VARCHAR(500)
DECLARE @num INT=1, @col_name VARCHAR(100) = NULL, @cnt INT

WHILE(@num<=3)
BEGIN
SELECT @col_name = name FROM sys.columns 
          WHERE object_id = OBJECT_ID('testing') and column_id = @num
SET @cnt = 0
SET @sql = '
IF NOT EXISTS(SELECT 1 FROM testing WHERE ' + @col_name + ' = 0) SET @cnt = 1'

EXEC sp_executesql @sql, N'@cnt INT OUT', @cnt OUT

IF @cnt = 1
    SET @list = COALESCE(@list + ',', '') + @col_name

SET @num = @num+1
END

SET @sql = '
SELECT ' + @list + ' FROM testing'
EXEC(@sql)

OUTPUT:

val1    val3
1       1
1       1
1       1

Upvotes: 1

Related Questions