Better way to write this query without calling a lookup for each column

• OlyKLin

  Ten Centuries

  Points: 1333

  More actions

  April 16, 2014 at 7:25 pm

  #98881

  Hi All

  I have a query that needs to evaluate a set of columns (up to 350) and put either a value that represents an active color when reporting or a product number if it exists or just blank.

  I have one table and another lookup table

  I'm blanking on using the lookup table in a join -- I'm doing something wrong so I've lowered myself to doing it this way:

  SELECT KeyValue, Name, Title, Board,

  ORDER4 =

  WHEN ORDER4 IS NULL AND (

  SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 4) = 1

  THEN

  'Z_'

  ELSE ORDER4

  ORDER5 =

  WHEN ORDER5 IS NULL AND (

  SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 5) = 1

  THEN

  'Z_'

  ELSE ORDER6

  ORDER5 =

  WHEN ORDER6 IS NULL AND (

  SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 6) = 1

  THEN

  'Z_'

  ELSE ORDER6

  .

  .

  .

  .

  ORDER350 =

  WHEN ORDER350 IS NULL AND (

  SELECT LKTable.Flag FROM LKTable_A WHERE KeyValue= T.KeyValue AND LKTable.ItemNumber = 350) = 1

  THEN

  'Z_'

  ELSE ORDER350

  FROM MainTable T

  They both have the KeyValue column to Join on but that's it. There's got to be a better way to do this and I don't want to use a function b/c that will slow the query down as well.

  Any suggestions would be great

  Thanks!

• Mansfield

  SSCrazy

  Points: 2396

  More actions

  April 16, 2014 at 8:45 pm

  #1706635

  Details a bit sparse, but try pivot the lookup table so that you can compare column to column?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  April 17, 2014 at 4:24 am

  #1706691

  Mansfield (4/16/2014)

  ---

  Details a bit sparse, but try pivot the lookup table so that you can compare column to column?

  Like this?

  SELECT

  KeyValue,

  Flag4 = MAX(CASE WHEN ItemNumber = 4 AND Flag = 1 THEN 'Z_' END), -- non-matches will be assigned NULL

  Flag5 = MAX(CASE WHEN ItemNumber = 5 AND Flag = 1 THEN 'Z_' END),

  Flag6 = MAX(CASE WHEN ItemNumber = 6 AND Flag = 1 THEN 'Z_' END),

  .

  .

  .

  Flag350 = MAX(CASE WHEN ItemNumber = 350 THEN Flag END)

  INTO #LKTable_A

  FROM LKTable_A

  GROUP BY KeyValue

  SELECT KeyValue, Name, Title, Board,

  ORDER4 = COALESCE(ORDER4, a.Flag4),

  ORDER5 = COALESCE(ORDER5, a.Flag5),

  ORDER6 = COALESCE(ORDER6, a.Flag6),

  .

  .

  .

  ORDER350 = COALESCE(ORDER350, a.Flag350)

  FROM MainTable T

  INNER (or left?) JOIN #LKTable_A a ON a.KeyValue= T.KeyValue

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden