SQL Server Performance

Select Query assistance

Discussion in 'General Developer Questions' started by sn_001, Apr 23, 2008.

  1. sn_001 New Member

    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.


  2. Adriaan New Member

    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.
  3. sn_001 New Member

    Thanks Adriaan i'll take a search online.

Share This Page