Compare Records in Table On coloumns

• rav999

  Old Hand

  Points: 398

  More actions

  August 15, 2010 at 2:31 am

  #224869

  I have a table with 5 columns. My requirement is if record in each column is equal to one 1 then it has to print true or it should print false.

• Ron McCullough

  SSC Guru

  Points: 63877

  More actions

  August 15, 2010 at 8:41 am

  #1207086

  Is this what you require?

  CREATE TABLE #T(C1 INT, C2 INT, C3 INT, C4 INT, C5 INT)

  INSERT INTO #T

  SELECT 1,1,1,1,0 UNION ALL

  SELECT 1,1,1,0,1 UNION ALL

  SELECT 1,1,0,1,1 UNION ALL

  SELECT 1,0,1,1,1 UNION ALL

  SELECT 0,1,1,1,1 UNION ALL

  SELECT 1,1,1,1,1

  SELECT C1 + C2 + C3 + C4 + C5 AS 'Sum',

  CASE C1 + C2 + C3 + C4 + C5 WHEN 5 THEN 'True' ELSE 'False'

  END AS Ans

  FROM #T

  Results:

  SumAns

  4 False

  4 False

  4 False

  4 False

  4 False

  5 True

  Or if you need to return the column values you could:

  SELECT C1,C2,C2,C4,C5,C1 + C2 + C3 + C4 + C5 AS 'Sum',

  CASE C1 + C2 + C3 + C4 + C5 WHEN 5 THEN 'True' ELSE 'False'

  END AS Ans

  FROM #T

  Which returns:

  C1C2C2C4C5SumAns

  111104False

  111014False

  111114False

  100114False

  011114False

  111115True

  If everything seems to be going well, you have obviously overlooked something.

  Ron
  
  Please help us, help you -before posting a question please read[/url]
  Before posting a performance problem please read[/url]

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  August 15, 2010 at 9:41 pm

  #1207141

  bitbucket-25253 (8/15/2010)

  ---

  Is this what you require?

  CREATE TABLE #T(C1 INT, C2 INT, C3 INT, C4 INT, C5 INT)

  INSERT INTO #T

  SELECT 1,1,1,1,0 UNION ALL

  SELECT 1,1,1,0,1 UNION ALL

  SELECT 1,1,0,1,1 UNION ALL

  SELECT 1,0,1,1,1 UNION ALL

  SELECT 0,1,1,1,1 UNION ALL

  SELECT 1,1,1,1,1

  SELECT C1 + C2 + C3 + C4 + C5 AS 'Sum',

  CASE C1 + C2 + C3 + C4 + C5 WHEN 5 THEN 'True' ELSE 'False'

  END AS Ans

  FROM #T

  Results:

  SumAns

  4 False

  4 False

  4 False

  4 False

  4 False

  5 True

  Or if you need to return the column values you could:

  SELECT C1,C2,C2,C4,C5,C1 + C2 + C3 + C4 + C5 AS 'Sum',

  CASE C1 + C2 + C3 + C4 + C5 WHEN 5 THEN 'True' ELSE 'False'

  END AS Ans

  FROM #T

  Which returns:

  C1C2C2C4C5SumAns

  111104False

  111014False

  111114False

  100114False

  011114False

  111115True

  Still think that fills the requirement? 🙂

  CREATE TABLE #T(C1 INT, C2 INT, C3 INT, C4 INT, C5 INT)

  INSERT INTO #T

  SELECT 1,2,1,1,0 UNION ALL

  SELECT 1,2,1,0,1 UNION ALL

  SELECT 1,2,0,1,1 UNION ALL

  SELECT 1,0,2,1,1 UNION ALL

  SELECT 0,2,1,1,1 UNION ALL

  SELECT 1,1,1,1,1

  SELECT C1 + C2 + C3 + C4 + C5 AS 'Sum',

  CASE C1 + C2 + C3 + C4 + C5 WHEN 5 THEN 'True' ELSE 'False'

  END AS Ans

  FROM #T

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  August 15, 2010 at 9:43 pm

  #1207142

  vrkn82 (8/15/2010)

  ---

  I have a table with 5 columns. My requirement is if record in each column is equal to one 1 then it has to print true or it should print false.

  There's some confusion in my mind about what you want. Are you saying you really only want to return a single row with 5 columns in it that say True or False with "True" meaning the number "1" only appeared in that column a single time no matter how many rows are store in the table?

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• malaytech2008

  Valued Member

  Points: 65

  More actions

  August 16, 2010 at 10:53 pm

  #1207652

  What I understand if there is value in each column then return true if not then return false.

  select Col1, case when coalesce(COl1,'')='' then false else true as Status from Table_Name

  Thanks,

  Malay

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  August 17, 2010 at 8:22 am

  #1207908

  malaytech2008 (8/16/2010)

  ---

  What I understand if there is value in each column then return true if not then return false.

  select Col1, case when coalesce(COl1,'')='' then false else true as Status from Table_Name

  Thanks,

  Malay

  That's not what I got out of the OP at all. Guess we need to wait and see if we hear from the OP again.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Tarun Jaggi

  Hall of Fame

  Points: 3365

  More actions

  August 25, 2010 at 2:48 am

  #1211495

  CREATE TABLE #T(C1 INT, C2 INT, C3 INT, C4 INT, C5 INT)

  INSERT INTO #T

  SELECT 1,1,1,1,0 UNION ALL

  SELECT 1,1,1,0,1 UNION ALL

  SELECT 1,1,0,1,1 UNION ALL

  SELECT 1,0,1,1,1 UNION ALL

  SELECT 0,1,1,1,1 UNION ALL

  SELECT 1,1,1,1,1

  SELECT C1 & C2 & C3 & C4 & C5 AS 'CheckCol',

  CASE C1 & C2 & C3 & C4 & C5 WHEN 1 THEN 'True' ELSE 'False'

  END AS Ans

  FROM #T

• DougGifford

  SSCommitted

  Points: 1815

  More actions

  August 26, 2010 at 12:46 pm

  #1212495

  vrkn82 (8/15/2010)

  ---

  I have a table with 5 columns. My requirement is if record in each column is equal to one 1 then it has to print true or it should print false.

  I assume OP wants 'True' if a 1 and 'False' if anything else.

  Here is another way to look at it.

  CREATE TABLE #T(C1 INT, C2 INT, C3 INT, C4 INT, C5 INT)

  INSERT INTO #T

  SELECT 1,2,1,1,0 UNION ALL

  SELECT 1,2,1,0,1 UNION ALL

  SELECT 1,2,0,1,1 UNION ALL

  SELECT 1,0,2,1,1 UNION ALL

  SELECT 0,2,1,1,1 UNION ALL

  SELECT 1,1,1,1,1

  Select case C1

  when 1 then 'True'

  else 'False'

  end as C1,

  case C2

  when 1 then 'True'

  else 'False'

  end as C2,

  case C3

  when 1 then 'True'

  else 'False'

  end as C3,

  case C4

  when 1 then 'True'

  else 'False'

  end as C4,

  case C5

  when 1 then 'True'

  else 'False'

  end as C5

  from #T

  Which returns:

  C1C2C3C4C5

  TrueFalseTrueTrueFalse

  TrueFalseTrueFalseTrue

  TrueFalseFalseTrueTrue

  TrueFalseFalseTrueTrue

  FalseFalseTrueTrueTrue

  TrueTrueTrueTrueTrue

  "When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
  "Robert A. Heinlein"