CODEWITHSUNDEEP
sql-serverreporting-services
Ole Lynge
Ole Lynge

Reputation: 4587

SQL Server Reporting wrap output rows

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...)

Upvotes: 1

Views: 265

Answers (3)

Ole Lynge
Ole Lynge

Reputation: 4587

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

Upvotes: 0

Raj More
Raj More

Reputation: 48014

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

Upvotes: 1

Jeremy Stein
Jeremy Stein

Reputation: 19661

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

Upvotes: 1

Related Questions