How to optimize SQL query to achieve the minimum execution time

Question

I have a simple table 'TABLE_1'

Org   Customer   Code   Ordered   Deleted   Confirmed

RU     Cust_1      A      1000       800        200 
RU     Cust_2      B      300        0          300
US     Cust_3      C      800        100        700
RU     Cust_4      B      100        100        0
US     Cust_5      C      400        200        200 
RU     Cust_6      B      500        300        200   

Now I need to transform this table for those rows, where 'Deleted'<>0 like

Org   Code    Customers          Ordered   Confirmed 

RU     A      Cust_1               1000       200
RU     B      Cust_4, Cust_6       600        200
US     C      Cust_3, Cust_5       1200       900

I'm using following query and function

SELECT T1.Org,
       T1.Code,
       dbo.FUNC(T1.Code, T1.Org) AS 'Customers',
       'Ordered' = (SELECT SUM(Ordered) FROM TABLE_1 AS T2 WHERE T2.Customer = T1.Customer AND T2.Code = T1.Code AND T2.Deleted<>0),
       'Confirmed' = (SELECT SUM(Confirmed) FROM TABLE_1 AS T3 WHERE T3.Customer = T1.Customer AND T3.Code = T1.Code AND T3.Deleted<>0)
FROM TABLE_1 AS T1 
WHERE T1.Deleted <> 0

Function 'FUNC':

ALTER FUNCTION [dbo].[FUNC] (@c VARCHAR(MAX), @org VARCHAR(MAX))
RETURNS VARCHAR(MAX) AS BEGIN
DECLARE @p VARCHAR(MAX) ;
SET @p = '' ;
SELECT @p = @p + T1.Customer + ', '
FROM TABLE_1 AS T1
WHERE T1.Code = @c AND T1.Org = @org AND T1.Deleted <> 0
GROUP BY T1.Customer
RETURN SUBSTRING(@p, 1, LEN(@p) - 1)
END

I think this is not the best way how to get result, especially if I have a large table. Is there a better solution for this purpose?

EDIT: Table DDL example

CREATE TABLE [dbo].[TABLE_1](
[Org] [nchar](10) NULL,
[Customer] [nchar](100) NULL,
[Code] [nchar](10) NULL,
[Ordered] [decimal](18,1) NULL,
[Deleted] [decimal](18,1) NULL,
[Confirmed] [decimal](18,1) NULL) 
ON [PRIMARY]

Answer 1

You will be facing a RBAR "problem" wathever you choose to do. Yet, you might find nicer to use FOR XML PATH('') + OUTER APPLY instead of your function.

If you don't know about those, I'll write a piece of code to demonstrate the usage. But could you provide your table DDL first (+some rows).

Here it is:

SELECT
    T1.Org
    , T1.Code
    , ISNULL(STUFF(F.Customers, 1, 2, ''), '')  AS Customers
    , SUM(T1.Ordered) OVER (PARTITION BY T1.Customer, T1.Code) AS Ordered
    , SUM(T1.Confirmed) OVER (PARTITION BY T1.Customer, T1.Code) AS Confirmed
FROM TABLE_1 AS T1
OUTER APPLY (
    SELECT
        ', ' + T2.Customer
    FROM TABLE_1 AS T2
    WHERE T2.Code = T1.Code
    AND T2.Org = T1.Org
    AND T2.Deleted <> 0
    FOR XML PATH('')
) AS F(Customers)
WHERE T1.Deleted <> 0