Fetch Next

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
 
wingfieldpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Daniel ReynoldsSoftware Applications Developer / IntegratorCommented:

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.