?
Solved

Fetch Next

Posted on 2006-03-22
1
Medium Priority
?
1,114 Views
Last Modified: 2008-03-06
Hello,
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
purchaseorder,newitem,item1,item1Desc,Newitem,item2,item2Desc,Newitem,Item3,item3desc

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?

Thanks
 
0
Comment
Question by:wingfieldp
1 Comment
 
LVL 17

Accepted Solution

by:
Daniel Reynolds earned 750 total points
ID: 16260345

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 = ''

DECLARE MyCursor CURSOR FOR
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
BEGIN

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

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

   WHILE @@FETCH_STATUS = 0
   BEGIN
           SELECT @bigRow = @bigRow + @item + ', ' + @itemdesc + ';'
           FETCH NEXT FROM myItemCursor into @item, @itemdesc
   END

   CLOSE  myItemCursor
   DEALLOCATE myItemCursor

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


0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

809 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