Concatenating Fields in a group 1

IanWaterman,
Check STUFF function.
I cannot check the syntax , but something like this should work for the grouping:

WITH tData (Account_Id,Company_Name,Branch_Code)
AS
(
Select Credit_Policy.Account_Id,
Insured_Associates.Company_Name,
Branch.Branch_Code
From ((Credit_Policy Left Outer Join Insured_Associates On Credit_Policy.Account_Id = Insured_Associates.Insured_Id)
Inner Join Business On Credit_Policy.Business_Id = Business.Business_Id)
Inner Join Branch On Business.Branch_Id = Branch.Branch_ID
Where Credit_Policy.Account_Id = 272407 --@InsuredId --
And Branch.Branch_Code = 'LE' --@inputBranch --
And Credit_Policy.Policy_Status In ('I', 'V')
)

SELECT DISTINCT Account_Id,
Stuff((SELECT '/ ' + Company_Name
FROM tData b
WHERE b.Date = a.Date AND b.Account_Id=a.Account_Id
FOR XML Path('')), 1, 1, '') AS Companies, Branch_Code
FROM tData a;


You still need to add count ( which will be another join to tData)

You can use temp table instead of tData if you want.

Viewer, scheduler and manager for Crystal reports.
Send your report everywhere.

Ian,
If you would like an excellent explanation of the FOR XML PATH part of the solution (and the STUFF command), read Brad Schultz's page about lists at:
At hand, as I've just used it! Can never remember the syntax...

soi là, soi carré

Worked perfectly.

Thank you for your help

Ian