Want to Display All Values Of IN Statement.

• Jawad Ahmed Siddiqui

  Old Hand

  Points: 301

  More actions

  April 8, 2006 at 12:32 am

  #89915

  Hi

  TabA

  Col_1

  When I pass query

  Select Col_1 from TabA

  Where Col_1 in

  (value1, value2, value3, value4, value5, value6, value7, value8)

  It will show the Col_1 records that matches with values provided in ‘IN’ statement.

  But I want output as: all values provided in ‘IN’ statement in one column and there matches (& null if no match) in 2nd column.

  Required Out put

  IN Statement        Values Col_A Matches

  Values1                Matched values of Col_A

  Values2                Matched values of Col_A

  Values3                Matched values of Col_A

  Values4                No Match

  Values5                Matched values of Col_A

  Values6                No Match

  Values7                No Match

  Values8                No Match

  Wishes

  Jawad

   

   

• Jeff Moden

  SSC Guru

  Points: 1004409

  More actions

  April 8, 2006 at 1:29 pm

  #631628

  Untested but should work...

   SELECT d.Value AS [IN Statement],
          CASE 
              WHEN ta.Col_1 IS NULL
              THEN 'No Match'
              ELSE ta.Col_1
          END AS [Values Col_A Matches]
     FROM TabA ta
    RIGHT OUTER JOIN
          (--Derived table "d" contains the "IN values
           SELECT value1 UNION ALL
           SELECT value2 UNION ALL
           SELECT value3 UNION ALL
           SELECT value4 UNION ALL
           SELECT value5 UNION ALL
           SELECT value6 UNION ALL
           SELECT value7 UNION ALL
           SELECT value8
          )d --End derived table "d"
  ON d.Value = ta.Col_1

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