Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Posts
    30

    Unanswered: Help with an Idea

    I am using sql 2005 and i did some crazy as concatenating in my previous post .. i am trying xml path to do my concatenating .. so for example:

    USE RSSQLDB
    select ITEMID, STUFF(
    (SELECT ',' + a.ItemDesc AS [text()]
    from RSITEMDESCRIPTION a
    where a.ITEMID = b.ITEMID
    Order by a.ItemDesc
    for xml PATH('')),1,1,'' ) AS Item_Description
    from RSITEMDESCRIPTION b
    Where ItemID = 135823
    group by ITEMID
    ORDER BY ITEMID

    And this is the other

    USE RSSQLDB
    select ITEMID, STUFF(
    (SELECT ',' + a.ItemContent AS [text()]
    from RSITEMCONTENT a
    where a.ITEMID = b.ITEMID
    Order by a.ItemContent
    for xml PATH('')),1,1,'' ) AS Item_Content
    from RSITEMCONTENT b
    Where ItemID = 135823
    group by ITEMID
    ORDER BY ITEMID

    How can i merge these ??

    So that i can have ITEMID, ITEM_DESCRIPTION and ITEM_CONTENT


    RSITEMDESCRIPTION and RSITEMCONTENT arre the 2 tables.
    Last edited by drdre; 08-04-11 at 19:59.

  2. #2
    Join Date
    Feb 2011
    Posts
    30
    got it solved..


    USE RSSQLDB
    SELECT
    AccountID,
    Itemcode,
    UserField1,
    b.ITEMID,
    STUFF((SELECT',' + a.ItemDesc AS [text()] from RSITEMDESCRIPTION a where a.ITEMID = b.ITEMID Order by a.ItemDesc for xml PATH('')), 1, 1, '') AS Item_Description,
    STUFF((SELECT',' + c.ItemContent AS [text()] from RSITEMCONTENT c where c.ITEMID = b.ITEMID Order by c.ItemContent for xml PATH('')), 1, 1, '') AS Item_Content
    FROM
    RSITEMDESCRIPTION b
    INNER JOIN RSITEMCONTENT c ON (b.ItemID = c.ItemID)
    Left JOIN RSITEM ON (b.ItemID = RSITEM.ItemID)
    WHERE
    AccountID=63
    GROUP BY
    b.ITEMID,accountid, Itemcode,UserField1
    ORDER BY
    ITEMID

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •