SQL Group column data from joins

Question

I need some help with writing SQL query.

I've data in the following format coming from a query returned by joining multiple tables.

CustID  Name    AccNo       Bank
------  ----    -----       -----
 1      Varun   9848032919    CB
 1      Varun   9998887771   COB
 1      Varun   9988776655    CB
 2      Lokesh  9876543210   COB
 2      Lokesh  9282726252    CB
 3      aaaa    9181716151   COB

I would like the data to be formatted as below so that it would be easy to load into Crystal reports.

CustID  Name               AccNo                      Bank
------  ----               -----                      -----
  1     Varun   9848032919,9998887771,9988776655      CB,COB,CB
  2     Lokesh  9876543210,9282726252                 COB,CB
  3     aaaa    9181716151                            COB

I've looked at other answers which suggest using STUFF, XML PATH() but they are applicable only if data comes from one table. In my scenario data is retrieved by joining multiple tables.

May I know how to group by CustID and concatenate data in other columns as I desire?

Thanks a lot for your time!!!

EDIT: I'm looking for a SQL Server 2005 version answer

Answer 1

You can place the query inside a CTE, then apply FOR XML PATH on this CTE like this:

;WITH CTE AS (
  ... your query here
)
SELECT C.CustID, MAX(Name),
       STUFF((
          SELECT ', ' + + CAST(AccNo AS VARCHAR(MAX)) 
          FROM CTE 
          WHERE (CustID = C.CustID) 
          FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
       ,1,2,'') AS AccNo,
        STUFF((
           SELECT ', ' + + CAST(Bank AS VARCHAR(MAX)) 
           FROM CTE 
           WHERE (CustID = C.CustID) 
           FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
        ,1,2,'') AS Bank
FROM CTE C
GROUP BY C.CustID