Help in search specific query

Kenny Gua 291 Reputation points
2023-01-06T15:57:19.567+00:00

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

{count} votes

Accepted answer
  1. Yitzhak Khabinsky 20,021 Reputation points
    2023-01-06T18:52:55.967+00:00

    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 |  
    +---+---+---+---+---+---+---+---+  
    

2 additional answers

Sort by: Most helpful
  1. Viorel 83,016 Reputation points
    2023-01-06T16:51:30.647+00:00

    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".

    No comments

  2. Jingyang Li 4,521 Reputation points
    2023-01-06T17:01:36.383+00:00
    --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