November 4, 2004 at 3:32 pm
Table 1
col_1.......col_2
1000......ABC
Table 2
col_1.....code.....value
1000......M........200
1000......R.........300
1000......X........350
I want to join Table 1 w/ Table 2 and get all values where the code is either 'M' or 'R' but I only want 1 row in my result set.
November 4, 2004 at 6:10 pm
So you want
200, 300, ...
as your resultset? If so, what does table 1 do?
Cheers
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2004 at 6:35 pm
Table 1 has some more values that I need in my result set that I didn't show.
Table 1
col_1.......col_2
1000......ABC
Table 2
col_1.....code.....value
1000......M........200
1000......R.........300
1000......X........350
I want
col_1.......col_2.....code.....value
1000......ABC........M..........200
1000......ABC........R..........300
But I want 1 row. Maybe something like....
col_1.......col_2.....code_1.....code_2....value_1.....value2
1000........ABC........M..........R.........200........300
November 4, 2004 at 7:13 pm
It is called a pivot table and is simple enough, however I suggest for logical reasons do it this way.
SELECT
T1.Col_1,
T1.Col_2,
MAX(CASE WHEN T2.Code = 'M' THEN T2.Value ELSE NULL END) AS CODE_M,
MAX(CASE WHEN T2.Code = 'R' THEN T2.Value ELSE NULL END) AS CODE_R
FROM
Table1 T1
INNER JOIN
Table2 T2
ON
T1.Col_1 = T2.Col_1 AND
T1.Col_2 = T2.Col_2 AND
T2.Code IN ('M','R') -- This is to remove all invalid entries from the initial set to speed a bit.
GROUP BY
T1.Col_1,
T1.Col_2
The only thing is the highest existing value for code M will be in output column CODE_M and for thow with code R in CODE_R.
So if Say the values in addition to those listed were
TABLE 1
2000...XYZ
3000...CBS
TABLE 2
2000...XYZ...M...200
2000...XYZ...X...300
3000...CBS...X...100
3000...CBS...R...800
Then the total final result would be
Col_1 Col_2 Code_M Code_R
1000........ABC........200........300
2000........XYZ........200........NULL
3000........CBS........NULL.......800
Which is more the normal way to see.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply