Help in search specific query

Question

Create table Validate (A1 char(1), B char(1), C char(1), D char(1), E char(1), F char(1), G char(1), H char(1), I char(1), j char(1), K char(1), L char(1))
Insert into values ('N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N')

--Expected Result. (I don't want column which has value-'N'. I only want to see column which has value='Y'
B C D E F G H I J K
Y Y Y Y Y Y Y Y Y Y

Answer 1

Hi @Kenny Gua ,

Please try the following solution.
It is based on SQL Server XML and XQuery powers.

SQL

USE tempdb;  
GO  
  
DROP TABLE IF EXISTS #tmpTable;  
CREATE TABLE #tmpTable (A1 char(1), B char(1), C char(1), D char(1), E char(1), F char(1), G char(1), H char(1), I char(1), j char(1), K char(1), L char(1));  
Insert into #tmpTable VALUES   
('N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N'),  
('2','N','Y','N','Y','Y','Y','Y','Y','Y','Y','Y');  
  
DECLARE @sql NVARCHAR(1024) =  
N'SELECT ' + (  
SELECT STRING_AGG(name, ', ')  
FROM (  
SELECT name   
FROM tempdb.sys.columns  
WHERE object_id = object_id('tempdb..#tmpTable')  
EXCEPT  
SELECT DISTINCT x.value('local-name(.)', 'SYSNAME')  
FROM #tmpTable AS t  
   CROSS APPLY (SELECT t.* FOR XML PATH(''), TYPE, ROOT('root')) AS t1(c)  
   CROSS APPLY t1.c.nodes('/root/*[not(text()=("Y","Y Option"))]') AS t2(x)) z) + ' FROM #tmpTable;';  
  
EXEC sys.sp_executesql @sql;  

Output

+---+---+---+---+---+---+---+---+  
| C | E | F | G | H | I | j | K |  
+---+---+---+---+---+---+---+---+  
| Y | Y | Y | Y | Y | Y | Y | Y |  
| Y | Y | Y | Y | Y | Y | Y | Y |  
+---+---+---+---+---+---+---+---+  

Answer 2

Try one of approaches:

declare @sql varchar(max) = 'select ' +  
    stuff(  
        (  
            select ', ''Y'' as ' + t.c  
            from Validate  
            cross apply (values   
                (A,  'A', 1),  
                (B,  'B', 2),  
                (C,  'C', 3),  
                (D,  'D', 4),  
                (E,  'E', 5),  
                (F,  'F', 6),  
                (G,  'G', 7),  
                (H,  'H', 8),  
                (I,  'I', 9),  
                (J,  'J', 10),  
                (K,  'K', 11),  
                (L,  'L', 12)) t(v, c, n)  
            where t.v = 'Y'  
            order by n  
            for xml path('')  
        ), 1, 2, '')  
  
exec (@sql)  

At least one value must be "Y".

Answer 3

--Add a primary key column id  
Create table Validate (id char(1) primary key, A1 char(1), B char(1), C char(1)  
, D char(1), E char(1), F char(1), G char(1)  
, H char(1), I char(1), j char(1), K char(1), L char(1))  
Insert into Validate  
values ('1','N','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y','N')  
,('2','N','Y','N','Y','Y','Y','Y','Y','Y','Y','Y','N')  
   
   
   
Declare @sqlUnpivot as NVarchar(4000)  
Declare @ColsUnpivot as NVarchar(4000)  
   
Declare @sqlPivot as NVarchar(4000)  
Declare @ColsPivot as NVarchar(4000)  
   
   
Set @ColsUnpivot=null  
   
Select @ColsUnpivot =  COALESCE(@ColsUnpivot + ', ', '') + '('+QUOTENAME(COLUMN_NAME,'''')+',' + QUOTENAME(COLUMN_NAME)+')'  
FROM   [INFORMATION_SCHEMA].[COLUMNS]  
WHERE TABLE_NAME='Validate' and COLUMN_NAME <> 'id'  
   
   
Set @ColsPivot=null  
Set @ColsPivot = STUFF((Select DISTINCT ', ' + quotename(id ,']')   
FROM Validate   
FOR XML PATH (''),type).value('.','varchar(max)'), 1, 2, '')  
  
--- drop temp table if it exists  
   
 drop table  if exists tempMatrix  
   
 ----Unpivot query and load the result into a temp table  
   
Set @sqlUnpivot='Select [id],Cols,Vals   
into tempMatrix  
FROM Validate t  
CROSS APPLY (Values ' + @ColsUnpivot + ' )  d(Cols,Vals) '  
   
   
--Print @sqlUnpivot;  
exec (@sqlUnpivot)  
    
  
   
set @ColsPivot = STUFF((SELECT  ',' + 'Max(CASE WHEN Cols=' + quotename(Cols,'''') + ' THEN Vals else null end ) as ' + quotename(Cols,'[')  + char(10)+char(13)  
                             FROM (  
							 select Cols from tempMatrix where Cols<>'id'  
							 except   
							 select Cols from tempMatrix where Vals='N') t  
  
                             FOR XML PATH(''), TYPE).value('.', 'varchar(max)'), 1, 1, '');  
   
Set @sqlPivot  =N' Select id,'+   @ColsPivot   
+ ' from tempMatrix   
Group by id ';  
       
print @sqlPivot  
EXEC(@sqlPivot)  
  
   
drop table Validate