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

Question

Post reply

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!

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Mansfield SSCrazy Points: 2366 More actions · Answer 1

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 · Answer 2

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