Home Forums Programming General Better way to write this query without calling a lookup for each column RE: Better way to write this query without calling a lookup for each column

  • 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