CODEWITHSUNDEEP
sqlsql-server
su jeong
su jeong

Reputation: 33

How to search columns have specific data using SQL Server

I'm using SQL Server 2008 and I'm looking for a way to find column name.

v01 | v02 | v03
----+-----+--------
 y  | x   |  y

That's how my table looks.

And I want to get v01, v03 because they have y.

Is there any way to get this using SQL?

Upvotes: 1

Views: 76

Answers (3)

Sreenu131
Sreenu131

Reputation: 2516

Sample data

IF OBJECT_ID('dbo.Temptable')IS NOT NULL
DROP TABLE Temptable
;WITH cte(
v01 , v02 , v03)
AS
(               
SELECT  'y'  , 'x'   ,  'y'
)
SELECT * INTO Temptable FROM cte

By Using cross apply with dynamic sql

IF OBJECT_ID('dbo.Getdata')IS NOT NULL
DROP TABLE Getdata
DECLARE @SqlCoulmn NVARCHAR(max)

SELECT @SqlCoulmn='SELECT'+ STUFF((SELECT DISTINCT ', '+ReqriredCOulmns FROM
(
SELECT 'CASE WHEN '+ COLUMN_NAME+'='+'''Y''' + 'THEN '+COLUMN_NAME  +' ELSE NULL END AS '+COLUMN_NAME AS ReqriredCOulmns
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Temptable'
)dt
FOR XML PATH ('')),1,1,'') +'  INTO Getdata FROM Temptable'

EXEC (@SqlCoulmn)

DECLARE @Sql NVARCHAR(MAX) , @dynCoulmn NVARCHAR(MAX)

SELECT @dynCoulmn=STUFF((SELECT distinct ', '+TempCol  FROM
(
SELECT '('+COLUMN_NAME+','+''''+COLUMN_NAME+''''+')' AS TempCol
 FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='Getdata'
)dt2
FOR XML PATH ('')),1,1,'')

SET @Sql=' 
SELECT Col FROM Getdata
CROSS APPLY ( Values '+@dynCoulmn+'
            ) A (Val,Col)
            WHERE Val IS NOT NULL
'
PRINT @Sql
EXEC (@Sql)

Result Before Cross apply

v01 v02 v03
-----------
y   x   y

Result after Cross apply

Col
---
v01
v03

Upvotes: 0

Serkan Arslan
Serkan Arslan

Reputation: 13393

You can use UNPIVOT

DECLARE @MyTable TABLE (v01 varchar(10), v02 varchar(10), v03 varchar(10))
INSERT INTO @MyTable VALUES ( 'y', 'x', 'y')

SELECT Col FROM @MyTable UNPIVOT( Val FOR Col IN ([v01], [v02], [v03])) UNPVT
WHERE Val = 'y'

Result:

Col
-------
v01
v03

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You can use case and concatenate the values together:

select ((case when v01 = 'y' then 'v01;' else '' end) +
        (case when v02 = 'y' then 'v02;' else '' end) +
        (case when v03 = 'y' then 'v03;' else '' end)
       )

Upvotes: 1

Related Questions