Hi everyone, I was wondering if I could get some assistance, I need to run a select query but don't know if the outcome I’m after is possible. Below is a more simple layout of what i'm working with, there are three tables: 1st - Vendor: Vendor, Vendor_key, 2nd - Product: Product, Vendor_key, product_key, product_type_key 3rd - Rate: Rate, product_key, Each Vendor has multiple products with multiple product types as well. What i want todo is have a new column for each product type, but only one column for the Vendor. So the results would look something like: Vendor,product_1,product_2, bobs ,6.00 ,4.00 bills ,9.00 ,9.00 jims ,8.50 ,null What I've tried so far but is giving me multiples in the results is: SELECT V.Vendor as "Vendor", R.Rate as "Product", R_1.Rate as "Product_1" FROM Vendor V LEFT JOIN Product P ON V.vendor_key = P.vendor_key LEFT JOIN Rate R ON P.product_key = R.product_key LEFT JOIN Product P_1 ON V.vendor_key = P_1.vendor_key LEFT JOIN Rate R_1 ON P_1.produc_key = R_1.product_key WHERE P.product_type_key = '1' or P_1.product_type_key = '2' If you can assist in anyway it would be much appreciated.
Do a search for "cross tab query" either here or in Books Online. It takes a little more work in 7.0/2000 than in 2005. You may also want to look at client software that have this as a built-in option, like MS Access.
