sql - Avoiding Multiple Joins -
i have table (manufacturers):
manufacturer id ------------------ lagun 1 hurco 2 mazak 3 haas 4
then table (inventory):
shop lathe drillpress cnc mill id ------------------------------------------------- abc inc 2 1 3 3 1 veco 4 2 1 2 2
i need end with:
shop lathe drillpress cnc mill -------------------------------------------- abc inc hurco lagun mazak mazak veco haas hurco lagun hurco
i have this:
select shop, m1.manufacturer lathe, m2.manufacturer drillpress, m3.manufacturer cnc, m4.manufacturer mill inventory left join manufacturers m1 on m1.id = i.lstflowman left join manufacturers m2 on m2.id = i.lstfiltflowman left join manufacturers m3 on m3.id = i.lstfilterman left join manufacturers m4 on m4.id = i.lstemitman
i'm missing better way pivot or cross apply or something.
thanks @laudec, here sqlserver version:
select * ( select shop, keys, manufacturer (select shop, lathe,drillpress,cnc,mill inventory) unpivot (val keys in (lathe,drillpress,cnc,mill)) unpvt join manufacturers m on m.id=val ) pivot ( max(manufacturer) keys in (lathe,drillpress,cnc,mill) ) pp
Comments
Post a Comment