Execute the following script in Query Editor to generate a list of product codes for selected purchase orders. Note that cursors are not scalable, so performance issues should be taken into account.

USE AdventureWorks;

SET NOCOUNT ON

DECLARE @PurchaseList TABLE

(POID INT,

 ProductList VARCHAR(4096))

DECLARE @PurchaseOrderID INT, @ProductCode VARCHAR(12),

        @ProductList VARCHAR(4096)

-- Declare a cursor for purchase orders

DECLARE curOrders CURSOR FOR

    SELECT PurchaseOrderID

    FROM Purchasing.PurchaseOrderHeader

    WHERE YEAR(OrderDate)= 2003

    AND   MONTH(OrderDate)= 12

-- Loop through each purchase order in the selected range

OPEN curOrders

FETCH NEXT FROM curOrders INTO @PurchaseOrderID

WHILE (@@FETCH_STATUS = 0)

BEGIN

      SET @ProductList = ''

      -- Inner cursor for product list for purchase order

      DECLARE curProducts CURSOR FOR

            SELECT p.productNumber FROM Purchasing.PurchaseOrderDetail pd

                  INNER JOIN Production.Product p

                  ON pd.ProductID = p.ProductID

                  WHERE pd.PurchaseOrderID = @PurchaseOrderID

      -- Loop through the products on the purchase order

      OPEN curProducts

      FETCH NEXT FROM curProducts INTO @ProductCode

      WHILE (@@FETCH_STATUS = 0)

      BEGIN

        -- Build the csv list via string concatenation

            SET @ProductList = @ProductList + @ProductCode + ', '

            FETCH NEXT FROM curProducts INTO @ProductCode

      END

      CLOSE curProducts

      DEALLOCATE curProducts

      -- Insert product list into table variable

      INSERT INTO @PurchaseList

      VALUES (@PurchaseOrderID, @ProductList)

      FETCH NEXT FROM curOrders INTO @PurchaseOrderID

END

CLOSE curOrders

DEALLOCATE curOrders

-- Remove trailing commas

UPDATE @PurchaseList

SET ProductList = LEFT(RTRIM(ProductList), LEN(RTRIM(ProductList))-1)

WHERE RIGHT(RTRIM(ProductList),1)=','

-- Publish results

SELECT * FROM @PurchaseList

ORDER BY POID