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

    Unanswered: mssql help required

    I have 3 tables that i relate to objects in my DB.

    Rsitem
    AccountID
    ItemID
    ItemCode

    RsitemDescription
    ItemCode
    ItemDesc

    RsitemContent
    ItemCode
    ItemContent

    I want to genreate a result grid ItemId,ItemCode,ItemDesc,ItemContent.

    But my ItemDesc and ItemContent are multi rows per ItemID. Whats the best and most efficent way of doing this.

    I will be running the sql query against a db with over 1mil lines of data.

    Please advise

  2. #2
    Join Date
    Jul 2011
    Posts
    63
    Have you tried an inner join?

    Select *
    FROM Rsitem inner join (RsItemDescription inner join RsItemContent on RsItemDescription.ItemCode = RsItemContent.ItemCode)
    on Rsitem.ItemCode = RsitemDescription.ItemCode

  3. #3
    Join Date
    Feb 2011
    Posts
    30
    tkengo that does work..

    but i want to concatenate the results into one like for each ItemID, instead of having multiple lines.

    I should have stated that in my original post.

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    I'm tempted to state that it's best to do so in the presentation layer, buy you may have a look at Concatenating Row Values in Transact-SQL
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    Feb 2011
    Posts
    30
    roiac i am just running my queries directly against the db and doing a dumb of the results.

  6. #6
    Join Date
    Feb 2011
    Posts
    30
    WITH Ranked ( Itemcode, ShortDescription, USERFIELD1, USERFIELD2 ,USERFIELD3,USERFIELD4, ItemStatusDesc, rnk, ItemDesc, ItemContent)
    AS ( SELECT Itemcode
    , RSITEM.ItemDesc As ShortDescription
    , USERFIELD1
    , USERFIELD2
    , USERFIELD3
    , USERFIELD4
    , ItemStatusDesc
    , ROW_NUMBER() OVER( PARTITION BY Itemcode ORDER BY Itemcode )
    , CAST( rssqldb.dbo.RSITEMDESCRIPTION.ItemDesc AS VARCHAR(8000))
    , CAST( rssqldb.dbo.RSITEMCONTENT.ItemContent 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.RSITEMCONTENT
    ON rssqldb.dbo.RSITEM.ItemID = rssqldb.dbo.RSITEMCONTENT.ItemID
    INNER JOIN
    rssqldb.dbo.RSACCOUNT
    ON rssqldb.dbo.RSITEM.AccountID = rssqldb.dbo.RSACCOUNT.AccountID
    INNER JOIN rssqldb.dbo.RSITEMSTATUS
    ON rssqldb.dbo.RSITEM.ItemStatusID= rssqldb.dbo.RSITEMSTATUS.ItemStatusID
    Where rssqldb.dbo.RSITEM.ITEMCode='0000798'
    )

    , AnchorRanked ( Itemcode,ShortDescription, USERFIELD1, USERFIELD2,USERFIELD3,USERFIELD4,ItemStatusDesc, rnk, ItemDesc, ItemContent )
    AS ( SELECT Itemcode
    , ShortDescription
    , USERFIELD1
    , USERFIELD2
    , USERFIELD3
    , USERFIELD4
    , ItemStatusDesc
    , rnk
    , ItemDesc
    , ItemContent
    FROM Ranked
    WHERE rnk = 1
    )

    , RecurRanked ( Itemcode,ShortDescription, Userfield1,USERFIELD2,USERFIELD3,USERFIELD4,ItemSt atusDesc, rnk, ItemDesc, ItemContent )
    AS ( SELECT Itemcode
    , ShortDescription
    , USERFIELD1
    , USERFIELD2
    , USERFIELD3
    , USERFIELD4
    , ItemStatusDesc
    , rnk
    , ItemDesc
    , ItemContent
    FROM AnchorRanked
    UNION ALL
    SELECT Ranked.Itemcode
    , RANKED.ShortDescription
    , RANKED.USERFIELD1
    , RANKED.USERFIELD2
    , RANKED.USERFIELD3
    , RANKED.USERFIELD4
    , RANKED.ItemStatusDesc
    , Ranked.rnk
    , RecurRanked.ItemDesc + ', ' + Ranked.ItemDesc
    , RecurRanked.ItemContent + ', ' + Ranked.ItemContent
    FROM Ranked
    INNER JOIN
    RecurRanked
    ON Ranked.Itemcode = RecurRanked.Itemcode
    AND Ranked.rnk = RecurRanked.rnk + 1
    )
    SELECT Itemcode
    , ShortDescription
    , MAX( ItemDesc ) As LongDescription
    , MAX(ItemContent) As Content
    , UserField1
    , USERFIELD2
    , USERFIELD3
    , USERFIELD4
    , ItemStatusDesc As ItemStatus
    FROM RecurRanked
    GROUP BY Itemcode,ShortDescription, UserField1, USERFIELD2,USERFIELD3,USERFIELD4, ItemStatusDesc;


    The above is the coding that i have done.. but i run into a problem when the ItemDesc field is less populated than the Content field and vise versa. It would duplicate the data in the field till both ItemDesc and ItemContent are the same in length.

    Any suggestions on how i can correct this.

  7. #7
    Join Date
    Feb 2011
    Posts
    30
    Bumpo ?any one ?

Posting Permissions

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