I can't do this client side as my "client" is excel

Desire result is three fields returned

1,8,A;B
2,30,B;C;D

CREATE TABLE #Orders
(
Order_ID int
)

CREATE TABLE #OrderAccounts
(
Order_ID int,
Account char(1),
Amount int
)

INSERT INTO #Orders (Order_ID) VALUES (1)
INSERT INTO #Orders (Order_ID) VALUES (2)

INSERT INTO #OrderAccounts (Order_ID,Account, Amount) VALUES (1,'A',3)
INSERT INTO #OrderAccounts (Order_ID,Account, Amount) VALUES (1,'B',5)
INSERT INTO #OrderAccounts (Order_ID,Account, Amount) VALUES (2,'B',5)
INSERT INTO #OrderAccounts (Order_ID,Account, Amount) VALUES (2,'C',10)
INSERT INTO #OrderAccounts (Order_ID,Account, Amount) VALUES (2,'D',15)

SELECT
#Orders.Order_ID,
SUM(#OrderAccounts.Amount)
FROM #Orders
INNER JOIN #OrderAccounts ON #Orders.Order_ID = #OrderAccounts.Order_ID
GROUP BY #Orders.Order_ID

DROP TABLE #Orders
DROP TABLE #OrderAccounts

1. create an scalar function

create function fn_accounts_by_order (@orderid int)
returns varchar(8000)

as

begin

declare @accounts varchar(8000)

set @accounts = ''

select @accounts = @accounts + ', ' + account

from orderaccounts

where order_id = @orderid

return (@accounts)

end

2. change script

CREATE TABLE Orders

(

Order_ID int

)

CREATE TABLE OrderAccounts

(

Order_ID int,

Account char(1),

Amount int

)

set nocount on

INSERT INTO Orders (Order_ID) VALUES (1)

INSERT INTO Orders (Order_ID) VALUES (2)

INSERT INTO OrderAccounts (Order_ID,Account, Amount) VALUES (1,'A',3)

INSERT INTO OrderAccounts (Order_ID,Account, Amount) VALUES (1,'B',5)

INSERT INTO OrderAccounts (Order_ID,Account, Amount) VALUES (2,'B',5)

INSERT INTO OrderAccounts (Order_ID,Account, Amount) VALUES (2,'C',10)

INSERT INTO OrderAccounts (Order_ID,Account, Amount) VALUES (2,'D',15)

SELECT "OrderID" = Orders.Order_ID,

"Amount" = SUM(OrderAccounts.Amount),

"Accounts" = cast('' as varchar(8000))

into #queryresult

FROM Orders INNER JOIN

OrderAccounts ON Orders.Order_ID = OrderAccounts.Order_ID

GROUP BY Orders.Order_ID

update #queryresult

set Accounts = dbo.fn_accounts_by_order(orderid)

select *

from #queryresult

DROP TABLE Orders

DROP TABLE OrderAccounts

drop table #queryresult

3. execute script

4. enjoy

"Terri" <t@cybernets.com> escribi en el mensaje
news:f3kqul$hd8$1@reader2.nmix.net...