Fetch Next

Posted on 2006-03-22
Last Modified: 2008-03-06
I have two tables  1 containing purchase order no  and 1 containing the items purchased.(they are related with the purchase order no.)
I need to concatenate the Purhcase order and all the items purchased into 1 line.
each item will have an identifier to determine where the new item starts
so that it looks like this

I read about fetch next but I can do not know how to set this up..
Can anyone provide a sample on how I can accomplish this?

Question by:wingfieldp
    1 Comment
    LVL 17

    Accepted Solution


    Cursors can be rather slow, but the following transact sql should give you the idea on how to do this

    declare @myPONumber varchar(15)
               , @item varchar(20)
               , @itemDesc varchar(50)
               , @bigRow varchar(300)

    -- initialize variables
    SET @myPONumber = ''
    SET @item = ''
    SET @itemDesc = ''
    SET @bigRow = ''

    SELECT PurchaseOrder FROM POTable

    OPEN MyCursor
    FETCH NEXT FROM MyCursor into @myPONumber   --Select a PONumber into your variable

    WHILE @@FETCH_STATUS = 0    --if all is ok

       DECLARE myItemCursor CURSOR FOR
       SELECT item, itemdesc from ItemTable Where PurchaseOrder = @myPONumber

       OPEN myItemCursor
       FETCH NEXT FROM myItemCursor into @item, @itemdesc
       SELECT @bigRow = @myPONumber + ';'

               SELECT @bigRow = @bigRow + @item + ', ' + @itemdesc + ';'
               FETCH NEXT FROM myItemCursor into @item, @itemdesc

       CLOSE  myItemCursor
       DEALLOCATE myItemCursor

       FETCH NEXT FROM myCursor into @myPONumber  -- get the next ponumber and repeat until out of records
    CLOSE myCursor
    DEALLOCATE myCursor


    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now