asked on

Concatenate Several Records into One Field

I found this function online and now need help making it work. Here is my data:

Loan Number Category
12345 Approval missing
12345 Income_Misc
12345 Insurance_Hazard Insurance Premium: Require proof of paid
55555 HUD1_Misc
55555 Property_Misc
55555 Credit_Paid-off Accounts: Require evidence closed

Here's the query I currently have:

SELECT LoanNumber,
substring((SELECT ( ', ' + Category) FROM SummaryReportView t ORDER BY Category ASC FOR XML PATH( '' )), 3, 1000 ) AS Category
FROM LoanData

Results:
Loan Number Category
12345 Approval missing, Credit_Paid-off Accounts: Require evidence closed, HUD1_Misc, Income_Misc, Insurance_Hazard
Insurance Premium: Require proof of paid, Property_Misc
55555 Approval missing, Credit_Paid-off Accounts: Require evidence closed, HUD1_Misc, Income_Misc, Insurance_Hazard
Insurance Premium: Require proof of paid, Property_Misc

As you can see, the Category field is pulling Categories from all Loans. I need each loan to only have the categories on that loan's record.

Please help.
Thanks,
Scott

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Brian Crowe

DECLARE @MyTable TABLE
(
	LoanNumber		INT,
	Category		VARCHAR(100)
);

INSERT INTO @MyTable (LoanNumber, Category)
VALUES (12345, 'Approval missing'),
	(12345, 'Income_Misc'),
	(12345, 'Insurance_Hazard Insurance Premium: Require proof of paid'),
	(55555, 'HUD1_Misc'),
	(55555, 'Property_Misc'),
	(55555, 'Credit_Paid-off Accounts: Require evidence closed')

SELECT T.LoanNumber,
	STUFF(
	(
		SELECT ', ' + Category
		FROM @MyTable
		WHERE LoanNumber = T.LoanNumber
		ORDER BY Category
		FOR XML PATH('')
	), 1, 2, '') AS CategoryList
FROM @MyTable AS T
GROUP BY LoanNumber

Open in new window

Scott Williams

ASKER

Perfect Vitor. Thanks for this consolidated solution!