April 16, 2014 at 7:25 pm
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!
April 16, 2014 at 8:45 pm
Details a bit sparse, but try pivot the lookup table so that you can compare column to column?
April 17, 2014 at 4:24 am
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
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
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy