Concatenating Fields in a group 1

[IanWaterman]

I have a query

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')

which returns

272407	Co1	LE
272407	co2	LE
272407	co3	LE
272407	c04	LE
272407	co5	LE

I would like to modify it to return

272407 "Co1 / co2 / c03 / c04 / c05" 5 LE

Whre 5 is a simple count

Thank you

Ian

 

[RTag]

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.

http://www.R-Tag.com

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

 

[drlex]

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:

http://bradsruminations.blogspot.com/2009/10/making-list-and-checking-it-twice.html

At hand, as I've just used it! Can never remember the syntax...

soi là, soi carré

 

[IanWaterman]

Worked perfectly.

Thank you for your help

Ian