How to get comma separated values in query?

Question

Hi I have a table Called tbdSales

Brand     Cust_ID  Prd_ID

Aftron  44301   T3485
Aftron  44301   T0628
Aftron  44301   T2952
Aftron  44301   T1958
Aftron  44302   T1940
Aftron  44302   T1939
Aftron  44303   T2419
Aftron  44303   T2045

In this table I want the Product_ID in comma separated with group by the Brand & Cust_ID

I have produced the query as follows:

SELECT DISTINCT 
      Brand
    , Cust_ID
    , (
        SELECT DISTINCT second_id + ', ' 
        FROM tbdSales t2
        WHERE t2.Brand = t1.Brand AND t2.Cust_ID = t1.Cust_ID
        FOR XML PATH('')
    ) AS prd_ID into SalReport
FROM tbdSales t1 
GROUP BY Brand,Cust_ID

The above query is giving results. But, if the records are more (10,000) then it's taking much time like 5 mins.

Please let me know any other way for reducing query completion time.

Answer 1

Try this SQLFiddle example. It uses recursive query with CTE. For faster results you need indexes on Brand, Cust_ID, Prd_ID:

with t2 as 
( select t0.*,
  row_number() over (partition by Brand,Cust_id order by Prd_id asc) G_id
  from
 (
  select distinct Brand,Cust_id,Prd_id from tbdSales
 ) t0 

 ),
  t1 as (
    select t.*,
           cast(Prd_id as varchar(max)) as m2
     from t2 t where g_id=1

 union all
 select b.*,
        cast(c.m2+','+b.Prd_id as varchar(max)) as m2
     from t2 b
         inner join t1 c
             on (c.g_id+1 = b.G_id) 
                and (b.Brand=c.Brand)
                and (b.Cust_id=c.Cust_Id)


)
  select brand,cust_id,M2 as Prd_id from 
  (
  select t1.*, 
         row_number() over (partition by Brand,Cust_id order by g_id desc) rn 
             from t1
  ) t3 where rn=1
 order by Brand,Cust_id

Answer 2

SELECT distinct brand, Cust_ID, Replace( Replace( Replace(
                            (   select t2.Prd_ID
                                from @Sales as t2 
                                where t2.brand = t1.brand and t2.Cust_ID = t1.Cust_ID
                                For XML Raw)
                                 , '"/><row Prd_ID="', ', ')
                                 , '<row Prd_ID="', '')
                                 , '"/>', '') FROM  @Sales t1

Hi, try this.