I have a order note table that looks like this:
ORDER NOTE
10000 some notes
10000 another note
20000 a new note
30000 note 1
30000 note 2
I want to aggregate with one line per order followed by all related notes, like this:
ORDER NOTES
10000 some notes, another note
20000 a new note
30000 note 1, note 2
Is there an SQL statement that can help me?
revision ---
database is IBM db/2, but would prefer SQL ANSI solution if it exists.
If you are using MS Sql2005+ then this should work for you:
CREATE FUNCTION GetOrderNotes(@OrderNumber INT)
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @ReturnText NVARCHAR(MAX)
SET @ReturnText = ''
IF EXISTS(SELECT * FROM Orders WHERE OrderNumber = @OrderNumber)
BEGIN
SELECT
@ReturnText = COALESCE(@ReturnText,'') + ISNULL(Note,'') + ', '
FROM
Orders
WHERE
OrderNumber = @OrderNumber
END
RETURN @ReturnText
END
GO
/*THEN JUST CALL THIS FUNCTION FROM WITHIN YOUR QUERY*/
SELECT
OrderNumber
, dbo.GetOrderNotes(OrderNumber) AS [Notes Combined]
FROM
Orders
GROUP BY
OrderNumber
ORDER BY
OrderNumber
Obviously replace the table and column definitions as required but this should get you to where you need to.
I generally try and create functions for this sort of thing so that I don't have to keep writing out the same code/ can use it in multiple places. So if my requirements change then I can change it here and not have to change it in say half a dozen places.
