Show multiple order records in single row

Show multiple order records in single row

• LtCondor

  There is no easy way to do this in versions before SQL Server 2005. You could write a CLR aggregate in SQL Server 2005 that performs the string concatenation. But this method has it's own limitations. For example, you cannot sort the input to the aggregate or exceed 8000 bytes. You can also use PIVOT with ROW_NUMBER in SQL Server 2005. These type of operations are done easily and efficiently on the client-side. Best is to return the rows as is and perform the formatting on the client.

   

  You can use traditional SQL methods also but performance will be a major problem. You can use scalar UDFs in SQL Server 2000 but it has it's limitations and performance issues.

  
  
  

• Brad Alexander

  First, this is a bad thing to do:

  declare @orders nvarchar(3000)
  select @orders = isnull(@orders + ' ', '') + OrderField
  from orders where companyID = @companyID

  Though I have done it many times myself. If you need to order the output things can go haywire. Check this article: http://www.aspfaq.com/show.asp id=2529

  If you are using 2005, the solution using FOR XML works great and doesn't suffer any inconsistencies

  
  
  

• Tim5827

  I have a similar requirement to do this type of aggregate concatenation.  I used the Concatenate() aggregate that is in the 'StringUtilities' CLR sample.  It worked fine except that I hit the 8000 bytes limit that you mentioned.  My goal is to send the results of the concatenation to a text file.  I'm using SSIS to write the text file.  Would I have to write an application to loop through the rows and concatenate or maybe a custom SSIS data transformation to do the aggregate concatenation because of the 8000 bytes limit of the Concatenate() function   Thanks.
  

• _x3m

  Do this

  Create function dbo.fn_get_Orders(@CompanyID int) as
  begin
  declare @orders nvarchar(3000)
  select @orders = isnull(@orders + ' ', '') + OrderField
  from orders where companyID = @companyID
  return @Orders
  end

  This will return the @Orders value with a space delimited string of all the order numbers or whatever else. Then you can simply use it in a select statement

  eg

  select companyID, CompanyName, dbo.fn_get_orders(companyid) from Company

  Amb

  
  
• Show multiple order records in single row