Hi, i have my data set with ColA, ColB, and ColC among others. I want to use my data set, tble1 to join to another database table, tble2 on ColA, ColB, and ColC.
Now, in tble2, there are three fields of note, name, wordval, and numval, and what I’ve been doing in my script is something like the following, but i feel there’s got to be a better way than repetitive joins, and i’m hoping i wouldn’t need to use elaborate pivot functions.
Below is what i have been doing. The bold is to emphasize what i’m hoping to avoid.
select t1.*, t21.wordval name1, t22.wordval name2, t23.numval name3, t24.numval name4
from tble1 t1 join tble2 t21
on t1.cola=t21.cola and t1.colb=t21.colb and t1.colc=t21.colc and t21.name='name1’
join tble2 t22
on t1.cola=t22.cola and t1.colb=t22.colb and t1.colc=t22.colc and t22.name='name2’
join tble2 t23
on t1.cola=t23.cola and t1.colb=t23.colb and t1.colc=t23.colc and t23.name='name3’
join tble2 t24
on t1.cola=t24.cola and t1.colb=t24.colb and t1.colc=t24.colc and t24.name='name4’
Is there a better way? I feel like i shouldn’t have to join to the same table each time i want something from it, and i’m not even sure Pivot would work because some of the end values are numbers, some are strings, and the field to pivot on is a number too.
Can you try a case statement? Just join to the table once and then -
select
t1.*
,max(case when t2.name = ‘name1’ then wordval else ’ ’ end) over (partition by t1.cola, t1.colb, t1.colc) as name1
,max(case when t2.name = ‘name2’ then wordval else ’ ’ end) over (partition by t1.cola, t1.colb, t1.colc) as name2
,max(case when t2.name = ‘name3’ then numval else 0 end) over (partition by t1.cola, t1.colb, t1.colc) as name3
,max(case when t2.name = ‘name4’ then numval else 0 end) over (partition by t1.cola, t1.colb, t1.colc) as name4
from tble1 t1 join tble2 t2 on t1.cola = t2.cola and t1.colb = t2.colb and t1.colc = t2.colc
Separate consideration of how to join records from what you want to do after you join them.
You have table1, with colA, colB, colC [and a bunch of other fields].
You have table 2 with colA, colB, colC, name, wordval, numval [and a bunch of other fields]
Are there any duplicates of colA, colB, colC on either table? Make sure you understand what you are trying to do if there are multiple records or mismatches.
After your join, you now have a table with the fields from table1 [you want] and table2 [the fields you want].
If the values you are looking for are known, and few in number, then a CASE without the partition can be done.
CASE WHEN t2.name = ‘name1’ THEN t2.wordval ELSE NULL END AS name1
CASE WHEN t2.name = ‘name2’ THEN t2.wordval ELSE NULL END AS name2
CASE WHEN t2.name = ‘name3’ THEN t2.numval ELSE NULL END AS name3
CASE WHEN t2.name = ‘name4’ THEN t2.numval ELSE NULL END AS name4
You didn’t specify what value to give namen if t2.name <> namen, so I chose NULL.
If the values in t2.name are many, you can write some dynamic SQL to handle it. My last posts in the Beginner SQL thread have a PIVOT with a string value. I can’t seem to get STRING_AGG to work, but MAX does. I am not sure how to get the PIVOT to use two different fields. You may have to PIVOT twice (once with MAX(wordval) and a WHERE t2.name IN (list of names for wordval), and again with SUM(numval) and a WHERE t2.name IN (list of names for numval) ) and JOIN the results.