How to select the column names of columns with a specific value

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  July 19, 2017 at 1:33 pm

  #327520

  Hey guys Suppose I had the following table columns as well as the row:

  MyTable
  Col_A           Col_B          Col_C        Col_D
   Y                     N                 Y               N

  How could I select the column names which have the value 'N' into 1 column? The resultset would be

  Returned_Column
  Col_B Col_D

• Joe Torre

  SSChampion

  Points: 10248

  More actions

  July 19, 2017 at 1:51 pm

  #1951752

  Can you post DDL, create table and inserts with sample data?

• drew.allen

  SSC Guru

  Points: 76996

  More actions

  July 19, 2017 at 1:51 pm

  #1951753

  You've been around long enough to know how to post sample data and expected results.  Also, one row isn't sufficient.  Adding even one more row to the data creates an ambiguous situation.

  SELECT *
FROM ( VALUES
    ('Y', 'N', 'Y', 'N'),
    ('Y', 'N', 'N', 'N')
)v(Col_A, Col_B, Col_C, Col_D)

  /*    I don't think you really want these results,
    but there's no way to tell from your sample data. */
SELECT *
FROM ( VALUES
    ('Col_B Col_C Col_D')
)v(returned_columns)


  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• dndaughtery

  SSChampion

  Points: 11290

  More actions

  July 19, 2017 at 2:07 pm

  #1951758

  Sorry guys, it reformatted the layout that I was seeing when posting it.

  Table

  Create Table dbo.MyTable (
        ID int Identity(1,1) NOT NULL
        ,Col_A varchar(1) NOT NULL
       ,Col_B varchar(1) NOT NULL
        ,Col_C varchar(1) NOT NULL
       ,Col_D varchar(1) NOT NULL
)

  Insert into dbo.MyTable (Col_A, Col_B, Col_C, Col_D)
Values ('Y', 'N', 'Y', 'N')
, ('N','Y','Y','N')
,('Y','N','N','N')

  I would want to select the all rows including the ID and another column with the column names which contain the value 'N'

  Select ID, (all column names for this row that contain 'N') as N_Containing_Columns_For_Row

  Result Set

  1, Col_B Col_D
  2, Col_A Col_D
  3, Col_B Col_C Col_D

• Phil Parkin

  SSC Guru

  Points: 246985

  More actions

  July 19, 2017 at 2:12 pm

  #1951759

  DROP TABLE IF EXISTS #DodgyData;

  CREATE TABLE #DodgyData
(
  Id INT IDENTITY(1, 1)
, Col_A CHAR(1)
, Col_B CHAR(1)
, Col_C CHAR(1)
, Col_D CHAR(1)
);

  INSERT #DodgyData
(
  Col_A
, Col_B
, Col_C
, Col_D
)
VALUES
(
  'Y', 'N', 'Y', 'N'
)
,(
  'N', 'Y', 'Y', 'N'
)
,(
  'Y', 'N', 'N', 'N'
);

  SELECT
  dd.Id
,  CONCAT(IIF(dd.Col_A = 'N', 'Col_A ', ''), IIF(dd.Col_B = 'N', 'Col_B ', ''), IIF(dd.Col_C = 'N', 'Col_C ', ''), IIF(dd.Col_D = 'N', 'Col_D ', ''))
FROM #DodgyData dd;

  --Edit: Sorry, need to change the DROP IF EXISTS to work in 2012.

  The absence of evidence is not evidence of absence.
  Martin Rees

  You can lead a horse to water, but a pencil must be lead.
  Stan Laurel