Multiple record rows and fields in one field

  • Thread starter Thread starter BJ Freeman
  • Start date Start date
B

BJ Freeman

I have an Excel export I am creating in Stored proceedures.
I need to put multiple rows with multiple fields in to one column(field)
Ok I am in over my head.
I created a stored procedure I have included below.

I want to make one string to return to the caller.
I thought this would do it.
Select @lclStr=@lclStr+', '+@OrderString+','+ CAST(@Quantity as
varchar(10))
No Dice. any suggestions

this
PRINT 'qty: ' + CAST(@Quantity as varchar(10)) + ' ordernumber= '+
@OrderString

return this in the QA

qty: 2 ordernumber= 109439

qty: 1 ordernumber= COS




DECLARE @SKU varchar(50),
@UPCString varchar(1000),
@OrderString varchar(1000),
@Quantity real,
@lclStr Varchar(1000),
@QTystr varchar(50)


DECLARE OrderString_cursor CURSOR FOR

SELECT Products.SKU, Products.[UPC String], Products.OrderString,
[Order Details].Quantity
FROM dbo.[Order Details] INNER JOIN
dbo.OrdersOrderDetailList ON
dbo.[Order Details].OrderDetailID =
dbo.OrdersOrderDetailList.OrderDetailID
INNER JOIN
dbo.Products ON
dbo.[Order Details].ProductID = dbo.Products.ProductID
WHERE dbo.OrdersOrderDetailList.OrderID = '3'
ORDER BY dbo.OrdersOrderDetailList.OrderID


OPEN OrderString_cursor



-- Perform the first fetch and store the values in variables.

-- Note: The variables are in the same order as the columns

-- in the SELECT statement.



FETCH NEXT FROM OrderString_cursor

INTO @SKU,
@UPCString,
@OrderString,
@Quantity



-- Check @@FETCH_STATUS to see if there are any more rows to fetch.

WHILE @@FETCH_STATUS = 0

BEGIN
Select @lclStr=@lclStr+', '+@OrderString+','+ CAST(@Quantity as
varchar(10))
-- Concatenate and display the current values in the variables.
PRINT 'qty: ' + CAST(@Quantity as varchar(10)) + ' ordernumber= '+
@OrderString
PRINT @lclStr
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM OrderString_cursor
INTO @SKU,
@UPCString,
@OrderString,
@Quantity
END
PRINT @lclStr
CLOSE OrderString_cursor
DEALLOCATE OrderString_cursor
 
Here is my suggestion:

I would first export the rows and columns into Excel. Depending on your
needs, you could combine the columns before exporting to Excel. From there
I would use VBA to manipulate and combine the rows together by looping
through the records. This would move the processing away from SQL Server.
This is assuming there would be less than 65,000 rows of data.

Not knowing your DDL or the data stored in it - this is the best I can
suggest.

-Daran
 
Back
Top