Searching for a value across all columns of a single table

• Lee Hopkins

  SSC Eights!

  Points: 960

  More actions

  September 28, 2016 at 8:47 am

  #317862

  Ok i have comparsion table that has brought back all columns from a table on a local hq db and a remote location DB.

  There are 47 columns that have been compared and i have the following EXAMPLE

  col1 col2 col3 col4 col5 col6-------------------

  1 TrueTrueTrueTrueTrueTrue

  2 TrueTrueTrueTrueTrueTrue

  3 TrueTrueTrueTrueTrueTrue

  4 TrueTrueTrueFalseFalseTrue

  5 TrueTrueTrueTrue FalseTrue

  6 TrueTrueTrueFalseFalseTrue

  There are 200K rows some all true some having a single false for a given col.

  what is the easiest way to search the table with out a mile long where clause

  where (col1 = false or col2 = false or col3 = false....)

• drew.allen

  SSC Guru

  Points: 76985

  More actions

  September 28, 2016 at 9:16 am

  #1903690

  SELECT v.*

  FROM (

  VALUES

  (1, 'True', 'True', 'True', 'True', 'True', 'True'),

  (2, 'True', 'True', 'True', 'True', 'True', 'True'),

  (3, 'True', 'True', 'True', 'True', 'True', 'True'),

  (4, 'True', 'True', 'True', 'False', 'False', 'True'),

  (5, 'True', 'True', 'True', 'True', 'False', 'True'),

  (6, 'True', 'True', 'True', 'False', 'False', 'True')

  ) v(id, col1 , col2 , col3 , col4 , col5 , col6)

  CROSS APPLY (

  SELECT col1, col2, col3, col4, col5, col6

  EXCEPT

  SELECT 'True', 'True', 'True', 'True', 'True', 'True'

  ) f

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  September 28, 2016 at 9:56 am

  #1903705

  Different options.

  SELECT v.*

  FROM (

  VALUES

  (1, 'True', 'True', 'True', 'True', 'True', 'True'),

  (2, 'True', 'True', 'True', 'True', 'True', 'True'),

  (3, 'True', 'True', 'True', 'True', 'True', 'True'),

  (4, 'True', 'True', 'True', 'False', 'False', 'True'),

  (5, 'True', 'True', 'True', 'True', 'False', 'True'),

  (6, 'True', 'True', 'True', 'False', 'False', 'True')

  ) v(id, col1 , col2 , col3 , col4 , col5 , col6)

  WHERE CAST( col1 AS bit) & col2 & col3 & col4 & col5 & col6 = 0

  EDIT: Adding second option.

  SELECT v.*

  FROM (

  VALUES

  (1, 'True', 'True', 'True', 'True', 'True', 'True'),

  (2, 'True', 'True', 'True', 'True', 'True', 'True'),

  (3, 'True', 'True', 'True', 'True', 'True', 'True'),

  (4, 'True', 'True', 'True', 'False', 'False', 'True'),

  (5, 'True', 'True', 'True', 'True', 'False', 'True'),

  (6, 'True', 'True', 'True', 'False', 'False', 'True')

  ) v(id, col1 , col2 , col3 , col4 , col5 , col6)

  WHERE EXISTS( SELECT 1

  FROM (VALUES(col1), (col2), (col3), (col4), (col5), (col6))x(col)

  WHERE col = 'False')

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• ScottPletcher

  SSC Guru

  Points: 101098

  More actions

  September 28, 2016 at 12:21 pm

  #1903747

  I suggest using a CASE technique to add the desired matches because that method gives you great flexibility on specifically what values to match. The code is somewhat longer, but you can generate the code rather than writing it by hand. For example:

  SELECT ' CASE WHEN col' + RIGHT('0' + CAST(N AS varchar(2)), 2) + ' = ''False'' THEN 1 ELSE 0 END + '

  FROM dbo.tally

  WHERE N between 1 and 47

  ORDER BY N

  /*naturally remove the last "+" from the gen'd code yourself*/

  /*That is the general method I used to generate all the code below:*/

  SELECT c.*

  FROM #comparisons c

  WHERE

  CASE WHEN col01 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col02 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col03 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col04 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col05 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col06 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col07 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col08 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col09 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col10 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col11 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col12 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col13 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col14 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col15 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col16 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col17 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col18 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col19 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col20 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col21 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col22 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col23 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col24 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col25 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col26 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col27 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col28 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col29 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col30 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col31 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col32 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col33 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col34 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col35 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col36 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col37 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col38 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col39 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col40 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col41 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col42 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col43 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col44 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col45 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col46 = 'False' THEN 1 ELSE 0 END +

  CASE WHEN col47 = 'False' THEN 1 ELSE 0 END

  >= 1 /*at least one False value*/

  --= 0 /*no False value*/

  --= 5 /*exactly 5 False values*/

  IF OBJECT_ID('tempdb.dbo.#comparisons') IS NOT NULL

  DROP TABLE #comparisons

  DROP TABLE #comparisons

  CREATE TABLE #comparisons (

  id int NOT NULL,

  col01 char(5) NOT NULL,

  col02 char(5) NOT NULL,

  col03 char(5) NOT NULL,

  col04 char(5) NOT NULL,

  col05 char(5) NOT NULL,

  col06 char(5) NOT NULL,

  col07 char(5) NOT NULL,

  col08 char(5) NOT NULL,

  col09 char(5) NOT NULL,

  col10 char(5) NOT NULL,

  col11 char(5) NOT NULL,

  col12 char(5) NOT NULL,

  col13 char(5) NOT NULL,

  col14 char(5) NOT NULL,

  col15 char(5) NOT NULL,

  col16 char(5) NOT NULL,

  col17 char(5) NOT NULL,

  col18 char(5) NOT NULL,

  col19 char(5) NOT NULL,

  col20 char(5) NOT NULL,

  col21 char(5) NOT NULL,

  col22 char(5) NOT NULL,

  col23 char(5) NOT NULL,

  col24 char(5) NOT NULL,

  col25 char(5) NOT NULL,

  col26 char(5) NOT NULL,

  col27 char(5) NOT NULL,

  col28 char(5) NOT NULL,

  col29 char(5) NOT NULL,

  col30 char(5) NOT NULL,

  col31 char(5) NOT NULL,

  col32 char(5) NOT NULL,

  col33 char(5) NOT NULL,

  col34 char(5) NOT NULL,

  col35 char(5) NOT NULL,

  col36 char(5) NOT NULL,

  col37 char(5) NOT NULL,

  col38 char(5) NOT NULL,

  col39 char(5) NOT NULL,

  col40 char(5) NOT NULL,

  col41 char(5) NOT NULL,

  col42 char(5) NOT NULL,

  col43 char(5) NOT NULL,

  col44 char(5) NOT NULL,

  col45 char(5) NOT NULL,

  col46 char(5) NOT NULL,

  col47 char(5) NOT NULL

  )

  INSERT INTO #comparisons

  VALUES(0,'True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True'),

  (1,'True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','False','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True'),

  (2,'True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','False','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','True','False','True','True','True','True','True'),

  (47,'False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False','False')

  SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

• DesNorton

  SSC-Insane

  Points: 24387

  More actions

  September 29, 2016 at 12:10 pm

  #1904011

  SELECT *

  FROM YourTable

  WHERE 'False' IN (col1, col2, col3, col4, col5, col6, ... )

  ---

  How to post data/code on a forum to get the best help.