Sql Server Reporting wrap output rows

Question

With Sql Server Reporting Services I would like to generate a report with line details for some orders like this:

Order  Details
2315   1x5432 Apple Tree, 9x912 Roses, 1x13 Spade
2316   3x12 Lawn grass

How could I do that most effectively, either with transact sql or by using something built into Reporting Services? (I might end up with many orders so a call for each order line might be expensive...)

Answer 1

I've generally solved this kind of problem with a user-defined function.

CREATE FUNCTION GetDetails (@order_id INT) RETURNS NVARCHAR(1000) AS
BEGIN 
  DECLARE mycursor
  CURSOR READ_ONLY
  FOR SELECT description
  FROM OrderDetails
  WHERE order_id = @order_id

  DECLARE @description NVARCHAR(100)
  DECLARE @return_value NVARCHAR(1000)

  SET @return_value = ''
  OPEN mycursor
  FETCH NEXT FROM mycursor INTO @description
  WHILE @@fetch_status = 0
  BEGIN
    SET @return_value = @return_value + @description
    FETCH NEXT FROM mycursor INTO @description
    IF (@@fetch_status = 0) SET @return_value = @return_value + ', '
  END
  CLOSE mycursor
  DEALLOCATE mycursor
  RETURN @return_value
END;

Your query would then be:

SELECT order_id, GetDetails(order_id) as details
FROM Order

Answer 2

I solved it using COALESCE, inspired by this thread:

CREATE FUNCTION GetDetails(@OrderID varchar(10))
RETURNS varchar(1000) AS
BEGIN
  DECLARE @result varchar(1000), @delimiter char

  SET @delimiter = ','

  SELECT @result = COALESCE(@result + @delimiter, '') + Details
  FROM 
    (
      SELECT ProductNumber
        , Quantity + 'x' + ProductNumber + ' ' + ProductName AS Details
      FROM OrderDetail where OrderID = @OrderID
    ) OrderDetail
  ORDER BY ProductNumber

  RETURN @result
END

and then, as in the answer by Jeremy Stein:

SELECT OrderId, GetDetails(OrderId) AS Details
FROM Order

Answer 3

I do the same using FOR XML and STUFF

SELECT DISTINCT 
    Col1.Table_Name TableName, 
    STUFF 
    (
      (
      SELECT
      ', ' + CONVERT (VARCHAR, Col2.Column_name)
      FROM Information_Schema.Columns Col2
      WHERE Col1.Table_Name = Col2.Table_Name
      ORDER BY Col2.Table_Name
      FOR XML PATH('')
      ), 1, 2, ''
    ) AS ColumnNames
FROM Information_Schema.Columns Col1