Query detail as a field.

I have two tables(Order and OrderDetail) of master-detail relationship. I have a nchar field in the detail table called itemno. I want to query like:

Select Order.OrderNo, Order.Date, SUM(OrderDetail.ItemNo) as ItemNos,....

From Order Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID

Where Order.OrderID=10

so that the resulting field ItemNos will become a string in format "ItemNo01, ItemNo02, ItemNo03,...."

How can I write this query in T-SQL?

Thanks

write a UDF

something on the lines of

CREATE FUNCTION [dbo].[returnItemNoFormat]
(

@.ItemSum int

)
RETURNS string
AS
BEGIN

/*
DO ALL YOUR FORMATTING IN HERE AND THEN RETURN THE STRING
*/

RETURN @.RTN

END

then your query should look like:

Select

Order.OrderNo,
Order.Date,

dbo.returnItemNoFormat(SUM(OrderDetail.ItemNo)) as ItemNos,....

From Order

Inner Join OrderDetail on Order.OrderID=OrderDetail.OrderID

Where Order.OrderID=10