Results 1 to 4 of 4

Thread: SQL query help

  1. #1
    Join Date
    Feb 2011
    Posts
    30

    Unanswered: SQL query help

    I am trying to run a query against our database :

    SELECT RSSACCOUNT.AccountID, RSSACCOUNT.AccountName, RSSITEM.ObjectID, RSSITEM.ItemCode, RSITEMDESCRIPTION.ItemDesc
    FROM RSSITEM INNER JOIN
    RSSITEMDESCRIPTION ON RSSITEM.ItemID = RSSITEMDESCRIPTION.ItemID INNER JOIN
    RSSACCOUNT ON RSSITEM.AccountID = RSSACCOUNT.AccountID

    Where RSSACCOUNT.AccountID=63
    And RSSITEM.ObjectID=3

    now my problem lies is that our Database stores the ItemDescription on multiple lines .. so the above code gives me results like

    "
    AccountID AccountName ObjectID Itemcode ItemDescription
    63 Team 3 0057135 DOCUMENT TITLE: testing 12
    63 Team 3 0057135 PROJECT: N/A
    63 Team 3 0057135 EQUIPMENT NUMBER: N/A
    "
    What i want to do is that for every itemcode the itemdescription comes all out on just one line instead of multiple lines..

    Is this possible ? if so how..

  2. #2
    Join Date
    Jan 2003
    Location
    Nottinghamshire, UK
    Posts
    364
    Hi Drdre

    looks like you need to concatenate the ItemDescription together with a correlated subquery joined to your RSSITEM table with the ItemID

    There are a number of ways to concatenate the description onto one row from multiple rows, heres an example of the XML Blackbox Method which should get you started, sorry I don't have SQL on this Box so I can't write the code for you.

    Code:
    SELECT p1.CategoryId,
           ( SELECT ProductName + ',' 
               FROM Northwind.dbo.Products p2
             WHERE p2.CategoryId = p1.CategoryId
             ORDER BY ProductName
                FOR XML PATH('') ) AS Products
          FROM Northwind.dbo.Products p1
         GROUP BY CategoryId ;
    heres the link to this & other methods

    http://www.simple-talk.com/sql/t-sql...-transact-sql/

    Hope this helps

    GW
    "Everything should be made as simple as possible, but not simpler." - Albert Einstein
    "Everything should be made as complex as possible, so I look Cleverer." - Application Developer

  3. #3
    Join Date
    Feb 2011
    Posts
    30
    hrm.. interesting.. i will give it a read in the morning..

    hopefully i will understand it.

    thanks for the link.

  4. #4
    Join Date
    Feb 2011
    Posts
    30
    I ended up going the CTE route..

    WITH Ranked ( Itemcode, rnk, ItemDesc )
    AS ( SELECT Itemcode,
    ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode ),
    CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000) )
    FROM rssqldb.dbo.RSITEM INNER JOIN
    rssqldb.dbo.RSITEMDESCRIPTION ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMDESCRIPTION.ItemID INNER JOIN
    rssqldb.dbo.RSACCOUNT ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID
    Where rssqldb.dbo.RSACCOUNT.AccountID=63
    And rssqldb.dbo.RSITEM.ObjectID=3),
    AnchorRanked ( Itemcode, rnk, ItemDesc )
    AS ( SELECT Itemcode, rnk, ItemDesc
    FROM Ranked
    WHERE rnk = 1 ),
    RecurRanked ( Itemcode, rnk, ItemDesc )
    AS ( SELECT Itemcode, rnk, ItemDesc
    FROM AnchorRanked
    UNION ALL
    SELECT Ranked.Itemcode, Ranked.rnk,
    RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc
    FROM Ranked
    INNER JOIN RecurRanked
    ON Ranked.Itemcode = RecurRanked.Itemcode
    AND Ranked.rnk = RecurRanked.rnk + 1 )
    SELECT Itemcode, MAX( ItemDesc )
    FROM RecurRanked
    GROUP BY Itemcode;

    now just to refine it and do little mods..

    Thanks for the link.

Posting Permissions

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