Home Forums SQL Server 2008 T-SQL (SS2K8) Return 1 record with data from multiple tables RE: Return 1 record with data from multiple tables

  • March 30, 2013 at 11:51 am

    #1602253

    umarrizwan (3/29/2013)

    one easy solution, using xml path.

    SELECT P.PRODUCTID,P.PRODUCTNAME,C.CategoryID,C.CATEGORYNAME,

    STUFF((SELECT ',' + convert(varchar,PP.ProductQuantity)

    FROM ProductPricing PP

    WHERE PP.PRODUCTID=P.PRODUCTID

    FOR XML PATH('')),1,1,'') AS ProductQuantity,

    STUFF((SELECT ',' + convert(varchar,PP.ProductCost)

    FROM ProductPricing PP

    WHERE PP.PRODUCTID=P.PRODUCTID

    FOR XML PATH('')),1,1,'') AS ProductCost

    FROM PRODUCT P

    JOINCategories C ON C.CategoryID=P.CategoryID

    Although that code works, it doesn't come close the the requested output.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)